Learn About SQL Server DB Statistics

About

In any application persistence of data is required. Database plays a very important role to fill this gap. Microsoft SQL server is one such database. We all know a database contains multiple tables to manage the data for us. However apart from Tables many more entities exist in these databases.

The attached utility can help a developer or a DBA for get more insight into the database they are working on. There are many aspects related to databases, however this utility is the first step towards knowing a few general statistics about the database. This article  covers:

• Prerequiste for the Utility
• How to use it
• Results
• What Next

Prerequisite for the Utility

• This utility has been created using .net version 4.0.0  so you only need .net framework 4.0 or higher on your machine to run this utility
  
  
• This utility requires connection to a database so you need a connection string, something like "Data Source=.\SQLEXPRESS; Initial Catalog=DatabaseName; Integrated Security=True; MultipleActiveResultSets=True". This is just a sample representation of a connection string. Make sure you have the correct connection string for your database available to you. Since I used Windows authentication this string does not have username and password and in your case this string can contain username and password also.
  
  
• You also need MSDB database connection string. A few of the statistics will be picked from this database. As part of any fresh installation of SQL server you always get MSDB database. Sample connection string would be like "Data Source=.\SQLEXPRESS; Initial Catalog=MSDB; Integrated Security=True; MultipleActiveResultSets=True". Since I used Windows authentication this string does not have username and password and in your case this string can contain username and password also.
  
  
• Since you are accessing 2 databases make sure you have appropriate access rights on these 2 databases
  

• You can run this utility from any machine on a network where database is accessible using connection string.

How to use

1. Attached utility contains only single .exe file. Simply copy the attached zip to any folder in your machine, say D:\DBstats\, unzip it, unlock it and you are ready to use the utility.
   
   
2. Open command prompt and go to folder where this utility is kept say D:\DBstats\.
   
   
   
   
3. Type DatabaseStatistics.exe to run this utility.
   
   
   
4. README about this utility is displayed. Read it to understand what this utility will do for you. Press any key to continue.
   
   
   
   
5. Enter connection string something like,
   
   
   Press Enter.
   
   
6. Enter MSDB connection string,
   
   
   
   Press Enter and utility is ready to get all the statistics it needs.

Results

Below is the statistics snapshot generated by this utility.

What Next

Work on the next version of this utility to get more statistics.