SQL Server DBA Utility Dashboard


By:   |   Updated: 2021-02-11   |   Comments (12)   |   Related: > Tools


Problem

You're being very productive when your boss calls and says: "What version of SQL is SERVERA?". Or maybe: "How big is database XXXX?". And of course: "The users say the app is running slow...".

You'd like to create a simple tool to show SQL Server information to the above questions. But delving into the different development environments is time consuming not to mention expensive. Setting them up with SQL Server Management Studio can be problematic and there are security issues when they run queries. Explaining what a cross product is would take time. Making web reports requires a big infrastructure setup, not to mention learning web development. You'd like to create a program that you can stick on their desktop. A quick windows form would fit the bill, but that's CSharp and Visual Studio and again a big investment in learning. Or maybe not...

Solution

In an earlier article Csharp Windows Form I showed how to make a simple Window's form with a CSharp compiler which is on your PC at this very moment. With this compiler you can take the CSharp code found below and compile it into a CSharp exe tool that you can give your power users. Its adaptable and auto formatting.

Your end user can choose a server, click a button, and see the following:

  1. General DB info including DB sizes and % full.
  2. What process are running and if there's a queue.
  3. See sp_who2 and all the spids connected. Shows if blocking is present.
  4. See what jobs are on the server and when they last ran.
  5. Top wait statistics.

Step 1: Find the CSharp compiler aka the csc.exe program

Run "dir csc.exe /s" at the root or C: and the pick one to compile with.

The compiler I used was: C:\Windows\Microsoft.NET\Framework64\v3.5\csc.exe.

I have about a dozen csc.exe compilers on my pc. I'm not sure why I have so many. I never installed any CSharp stuff on my pc. My guess is that new versions of SQL Server Management Studio or SQL Server Express come with an updated compiler.

Step 2: Put the code into .cs file and compile

Put the code into a text file called "Csharp_SQL_TOOLKIT_Combo.cs" (or whatever you want to call it).

Compile it with the csc.exe compiler of your chose.

Note: before you compile, you can put a list of your own servers in the combo box. Look for "///AAA" in the code and add your servers.

You can download the entire script here.

I put everything in a folder "C:\Junk\Csharp2". Go to that folder and compile it like this:

C:\Windows\Microsoft.NET\Framework64\v3.5\csc.exe Csharp_SQL_TOOLKIT_Combo.cs

Step 3: Run the Executable

 At a DOS prompt run the following or just double click on the exe.

c:\Junk\Csharp2\Csharp_SQL_TOOLKIT_Combo.exe

Enter a SQL Server or choose a server from the dropdown you added before compiling the code. Below are examples of mine, note, I crossed out the server name in the examples.

Note, this uses a trusted connection to run the queries. You just need to enter a server name and then click on a button and the data will be retrieved. If you don't have access or privilege to run the code it will not return data and return an error.

SQL Server Database Information

The first button is "DB Info". This runs a query that shows database information for all databases in the instance. This outputs database name, file group, size, percent used, last backup date and physical location of the database files.

SQL Server Database Information

Current SQL Server Processes

The second button is "Processes". This will run a query that shows what queries are actually running on the server.

It's been my experience that a slow server has queries backing up. This will show you what's backed up and if there is any blocking going on. The query with the biggest elapsed micro seconds is probably your culprit. It also shows the first 200 characters of the text of the query.

I've used this query many times to examine slowness on a server. With this button, your power user might be able to see the culprit which may disappear by the time he calls you to tell you its slow.

Current SQL Server Processes

SQL Server sp_who2 Results

The next button is "Sp_Who". This button executes sp_who2 and shows all connections for the SQL Server instance.

This is used to show blocking and to give the end user an idea of how many connections are hitting the server. It also shows the SQL connections associated with the engine, these are SPIDs with a value of 50 and less.

SQL Server sp_who2 Results

SQL Server Agent Job History

The next button is "Job History".  This will retrieve SQL Server Agent job history data.

End users may want to know if a job finished and when it finished. This button will show all enabled jobs who have a run that is recorded.  The output includes the job name, current status, run date, run time and duration in milliseconds.

SQL Server Agent Job History

SQL Server Wait Statistics

The next button is "Waits". This button retrieves Wait Statistics for the SQL Server instance.

This button may open a can of worms for the DBA. We all know something has to be the leading wait statistic, but is it relevant? The query for this output retrieves all wait stats.  There are several that are not that important to review, so the query could be expanded to exclude specific waits stats. I can't remember where I got this query, but somebody else wrote it. You could take a look at this article to get a different set of data and also filter out some of the wait stats. You could replace the query before compiling the code.

sql server wait stats

Summary

I've used this setup to create some reports for end users. If you have a query that someone wants to run, look at the button code and add another or modify the existing query. Put the query in the button pressed area and VOILA!

You can review the code before compiling and update any of the queries to ones you use most often.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Bernard Black Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

View all my tips


Article Last Updated: 2021-02-11

Comments For This Article




Tuesday, March 9, 2021 - 8:58:45 AM - Bernie Black Back To Top (88367)
I have no idea if this will work with Azure. I'm GUESSING it should since management studio can connect with "the cloud". I'm also guessing your connection strings may need to change.

Monday, March 8, 2021 - 7:15:33 AM - Divya Back To Top (88356)
Can we use Azure Managed instance or Azure SQL instance in comboBoxServ1.Items.Add area?

Monday, March 8, 2021 - 3:54:28 AM - Salam Back To Top (88353)
This is to Francis Apel, I visited SQLRanger, downloaded it, it seems a nice and might be a useful tool, downloaded it and after setup, it displays nothing in the monitor screen, no way to report back bugs, no support or help for the tool, getting "Unhandled exception on several when clicking on some tabs

Monday, March 8, 2021 - 3:49:41 AM - Salam Back To Top (88352)
Useful and nice tool, thanks

Tuesday, February 16, 2021 - 9:21:26 PM - Bernie Black Back To Top (88251)

10

Latest Microsoft .NET Framework 4.7 doesn't include C# compiler anymore.
Darshan, Try a newer/different compiler. From a blog:

But it can be installed as part of standalone MSBuild tool.

Go to https://www.visualstudio.com/downloads/, scroll down to "Other Tools and Frameworks" and choose "Build Tools for Visual Studio 2017".

Tuesday, February 16, 2021 - 9:09:10 PM - Bernie Black Back To Top (88250)

10

Darshan, I guess try another compiler....found this one in a blog...

Latest Microsoft .NET Framework 4.7 doesn't include C# compiler anymore.

But it can be installed as part of standalone MSBuild tool.

Go to https://www.visualstudio.com/downloads/, scroll down to "Other Tools and Frameworks" and choose "Build Tools for Visual Studio 2017".

Monday, February 15, 2021 - 7:09:07 AM - Darshan Back To Top (88239)
Hi Bernard,
Thanks for getting back to me on this.Correct me if I am wrong, steps which I had followed

1)Searched for the csc.exe file( i have only one on my machine)
.net version is v4.0.30319
2)Downloaded the script and pasted the same in Csharp_SQL_TOOLKIT_Combo.cs and
changed the server name in ///AAA in the code as suggested
3)Pasted the same in a folder "C:\Junk\Csharp2
4)When I run the exe from c:\Junk\Csharp2\Csharp_SQL_TOOLKIT_Combo.exe
it says me(This app Can't run on your PC)

can you help me on this please

Friday, February 12, 2021 - 10:14:32 AM - bernie black Back To Top (88230)
Darshan,

So you compile and the .exe doesn't run ? This hasn't happened to me. Maybe right-click the .exe, go to properties and hit the compatibility tab. Then try running it with another windows version. I've never done this but might get it going. Also, try compiling with another csc.exe. I had bunches on my pc and chose the one under the 2008 R2 path.

Friday, February 12, 2021 - 9:24:59 AM - Sharon Back To Top (88228)
Very nice!

Friday, February 12, 2021 - 7:09:30 AM - Francis Apel Back To Top (88227)
This is a nice way to store your often-used queries. Thank you. Also check out the free tool SQLRanger at SQLRanger.com. Very helpful!

Friday, February 12, 2021 - 6:43:48 AM - Darshan Pawar Back To Top (88226)
Hi Bernard,

When ever I run the exe I am getting the error as below "this app can't run on your pc"
Can you help me on this please.

Thursday, February 11, 2021 - 12:16:11 PM - Trina Black Back To Top (88221)
This is brilliant. Exactly what I have been wanting, but, not had time to find a solution. Excited to customize it with some of my buttons and queries.


download














get free sql tips
agree to terms