Systems Engineering and RDBMS

Archive for June 8th, 2007

Performance Dashboard Reports for SQL Server 2005

Posted by decipherinfosys on June 8, 2007

MSFT released a set of Performance Dashboard Reports post the release of Service Pack 2 for SQL Server 2005. You can use the SSMS (SQL Server Management Studio) client tool to look into the output of these reports which need to be categorized under the “Custom Reports” category. This feature was one of the new features that were introduced along with SP2. You do not need to have Reporting Services installed in order to use the Performance Dashboard Reports – these are just additional reports that can be viewed in addition to the ones that are already provided in SSMS. These reports are a welcome addition to the DBA/Database Developer arsenal for troubleshooting performance issues with the instance – you can find out the queries consuming the most CPU or causing the IO bottlenecks as well as missing indexes recommendations from the optimizer, blocking and latch contentions etc.. More on those features later on – firstly, if you have not already downloaded and installed the Dashboard Reports, you can do so from this link from MSFT:

The requirement for running these reports is that the Service Pack 2 must be installed on the instance that you wish to monitor. Once you have downloaded the files, you then need to run the Setup.sql file in SSMS to create the procedures, functions and views in the MSDB database. This needs to be done on every instance that you wish to moniitor. In this blog post, we will go over the installation of the Dashboard Reports and how to view them.

Once you have downloaded the msi package for the install (SQLServer2005_PerformanceDashboard.msi) and have installed it (the installation is a series of very simple steps), you will be ready to run the setup.sql script file which will be put in the same folder where the Performance Dashboard reports are installed. One thing to note here is that when you are doing the install, if you have a lot of instances that you want to monitor in your environment, you should/could chose to store the reports on a file share instead of the local drives. That way, you will not need to install this on all those N number of instances and can share them from a common place. Once the install is done, pick up the setup.sql script file and run it in SSMS on all the SQL Server instances that you want to monitor. The script then runs in the MSDB database and creates a set of stored procedures, views and functions –> these are the ones that are used by the Performance Dashboard Reports in order to build up the display and those DMVs and DMFs are pretty powerful and a good way to learn the system internals as well.

Once you are done with running the script, you can navigate to SSMS, connect to the instance, right click on the database that you want to monitor, select Reports, then Custom Reports and then pick up the “Performance_Dashboard_Main.rdl” report from the file-share or the local directory where you had done the install. Screenshot for that is shown below.

Once you select it, the main report that will appear will be like the one shown below. SSMS will remember this report and it will be available for you to look into at a later stage as well rather than requiring you to upload them again and again.

In the future blog posts, we will cover the reports themselves and how they can be used and which DMVs/DMFs they are based on.

Posted in SQL Server | 1 Comment »

More on sp_MSforeachTable

Posted by decipherinfosys on June 8, 2007

In one of our previous blog post, we had covered two un-documented but very handy system stored procedures in SQL Server. One of those was sp_MSforeachTable. Take a look at the post to see what this is for and what it can do for you. In this post, we are going to address the issue of collecting the data that is streamed through this command into a table so that we do not hit the 100 query result sets issue. In other words, if you run a command like:

EXEC sp_MSforeachtable @command1=’select ”?”, count(*) from ?’

and you have more than 100 tables in your schema, then you will get the error message:

“The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.”

Not to mention that the output is not readable since it is in the form of separate result sets. You can circumvent the error by chosing to run in a text mode but that is not desirable either since that still is not a readable format. Best thing to do would be to dump the records into a table and then read from it, example:

/*Create the global temporary table – you can choose to create a physical table if you want to preserve the records for longer*/

create table ##table (table_name varchar(50), num_rows int);

EXEC sp_MSForEachTable @command1=’INSERT INTO ##table(Table_Name,Num_Rows) select ”?”, count(*) from ? ‘;

/*Look at the record counts now*/

select * from ##table

Doing this, you can get away from the record-sets issue. For other ways of counting the number of records in SQL Server, you can look at our previous blog post here.

Posted in SQL Server | 2 Comments »