SQL server

Business Intelligence helper.

I have been doing some BI work lately and ran across this Visual Studio add on that helps with BI development. Thought I would share it with everyone.

The tool is called BIDS Helper.



MySQL, SQL server, SQLServerPedia Syndication

Logman for performance data collection.

While experimenting with Extended events, I ran across a tool call Logman that ships with windows. Another similar tool is Tracerpt (which I want to test out later). As per technet: Logman creates and manages Event Trace Session and Performance logs and supports many functions of Performance Monitor from the command line.

This tool can be used to create, collect, and query perfmon data right from the command line. Run the command logman query to see a list of data collector sets available on the system. I saw a ‘test’ data collector that I had previously created using perfmon. Run logman query “test” to see details about the data collectors and the counter-categories\counters that are setup.

From the command line, logman can be used to start and stop the collectors. Unfortunately the import\export feature is not there in XP or 2003. I checked a 2008 server, and it had the import\export feature. This is useful for moving around customized data collectors without having to manually create them on each server.

MySQL, SQL server

Geekbench benchmarking.

Geekbench is a good tool to benchmark your SQL server and put a number to it. The benchmark process is very quick and easy. Here is what I did:

  1. Go to the URL (http://www.primatelabs.ca/geekbench/) to download the setup file.
  2. I downloaded the free version which works on 32 bit systems. For 64 bit systems, a license has to be purchased.
  3. Once the setup file is downloaded, double click to run it.
  4. Follow the standard onscreen instructions to install the program. I chose not to create a start menu program group.
  5. Once installed, you can run the program as the last step of the installation, or run the program from the start menu, or go to the installed directory and run the Geekbench22.exe (program name may vary) program.

That’s it. Once run, the program will do its thing. Note the tip that says that Geekbench works best when it is the only program running. On my laptop the program ran for a little over a minute and gave it a score of 2912. I then ran in on a test server. It took around 3 minutes to finish and gave it a score of 2464.

The program has an option to upload your results online to Geekbench to be shared with everybody’s results. The program also gives some detailed information about processor and memory, but is agnostic to ownership details of the system (i.e. computer name, hostname, etc).

SQL server

SQL tools.

Troubleshooting SQL is difficult in itself, so it helps to have a couple of handy tools. Below are a list of some of the tools I find handy:

PAL: Performance analysis of logs. PAL can be used for SQL, and has two great functions. 1) To create the counters to collect perfmon logs. There was a time my manager came up to me and told me we had an urgent task to measure the health of the system. I was going to use perfmon, but what counters should I look for? Perfmon can help by providing the right counters to use. 2) To decipher and analyze perfmon logs using data visualization. I use PAL to load a perfmon log file, and PAL produces HTML output with text, based on common threshold, describing the health of the system.

SQL nexus: This is another open source tool.

SQL BPA: SQL Best Practice Adviser from Microsoft.

SQL server

Project Lucy.

I was checking out Project Lucy. It is a really smart idea and this is how it works:

  1. Download the exe from the project site and run it.
  2. This will create a plugin in SSMS (version 2008 onwards).
  3. If you has SSMS open, you will have to close and reopen it.
  4. Now right click on a server in object explorer and and you will see a new icon called ‘Project Lucy’. Click on this open up a dialog window.
  5. Specify your trace parameters and start your tracing.
  6. Your trace ends once the time/size you specified has been reached.

By default, the trace file will be stored on C:\Trace on the SQL server the trace is running on, but you can also specify a UNC path. You will need a login created on the project site and you will be uploading your trace file to this website. Once you have created a login and uploaded your trace file, then some crunching happens and you will have a smooth web GUI to explore the results of the trace.

I tested it out, and everything worked smoothly. I got excited a uploaded a couple of trace files to the site. The interface is smooth and has explanations with sqlpedia links

MySQL, SQL server

Powershell helper/IDE.

Powershell is (as named) powerful.  I have been using it for a bit and it is really easy to automate tasks across multiple servers using POSH (powershell). Now that POSH comes standard with SQL2008 and server 2008, availability is not an issue plus is it backwards compatible to a level. There is a free GUI out there called PowerGUI that makes it much easier to learn and develop with POSH. Click here for PowerGUI site link.

There is also SQLPSX (SQL Power Shell Extensions). Click here for SQLPSX codeplex link. It is also backwards compatible with SQL2000. Here is an excerpt description from the site: SQLPSX consists of 13 modules with 163 advanced functions, 2 cmdlets and 7 scripts for working with ADO.NET, SMO, Agent, RMO, SSIS, SQL script files, PBM, Oracle and MySQL and using Powershell ISE as a SQL and Oracle query tool. In addition optional backend databases and SQL Server Reporting Services 2008 reports are provided with SQLServer and PBM modules.

Now let get exploring with these two tools.

SQL server

SQL Best practice analyzer.

Another handy tool provided by Microsoft. Here is the link to SQL2005 BPA.

Description from MS: The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

I have used this tool and it produces informative reports on the SQL installation. Some of the tips it can provide are on security settings, performance suggestions, etc based on the best practices. SQL BPA even reads the logs to check for past errors and informs the operator on whether the server looks faulty. Even suggestions on disk sector alignment, performance issue related to logging, etc  is provided. Clicking on the rows will open up the help document related to the row clicked.