Wednesday, May 04, 2005

I was trying to determine whether I had put all of my tables on the correct filegroup and I wanted to list this. I came up with the following query to quickly list all the needed information.

SELECT o.Name as 'Table Name', g.GroupName
FROM sysobjects o
JOIN sysindexes i on i.id = o.id
JOIN sysfilegroups g on g.groupid = i.groupid
A lot of people tend to use DBCC FREEPROCCACHE when they are performance testing procedures. This is obviously a very good habit, however, when sharing a server with other databases you are clearing the cache for all databases. You can prevent this by using DBCC FLUSHPROCINDB(dbid) which only clears the cache for a specific database.

Tuesday, May 03, 2005

Found this old but yet very useful article in SQL Server Magazine (InstantDoc 40925).
It is an in depth study of DBCC SQLPERF (WAITSTATS) with a lot of information on what to monitor.

There are a couple of interesting formulas in the article that came to my attention.

Initial Compiles(%) = (SQL Compilations/sec - SQL Recompilations/sec) / Batch Requests/sec
Plan Reuse (%) = (Batch Requests/sec - Initial Compiles/sec) / Batch Requests/sec

To determine the optimal Packet Size use the following formula:
Average Number of Bytes per packet = Bytes Total/sec / Packets/sec