SQL Tuning Scripts

While tuning the SQL database for the project I am working on, I found these scripts by Brent Ozar that were extremely useful. These scripts rely on DMV on your SQL instance - so they are best run after you let your SQL databases run for a while and DMV data got flushed after a reboot. Brent also created videos explaining these scripts. Please note that all these scripts are Brent's creation - not mine.

FIND MISSING INDEXES
In the result, the "impact" column is the indicator how needed the index is. Brent recommends that 10 million or higher number means high - that the index is needed.

/* ------------------------------------------------------------------
-- Title: FindMissingIndexes
-- Author: Brent Ozar
-- Date: 2009-04-01 
-- Modified By: Clayton Kramer ckramer.kramer(at)gmail.com
-- Description: This query returns indexes that SQL Server 2005 
-- (and higher) thinks are missing since the last restart. The 
-- "Impact" column is relative to the time of last restart and how 
-- bad SQL Server needs the index. 10 million+ is high.
-- Changes: Updated to expose full table name. This makes it easier
-- to identify which database needs an index. Modified the 
-- CreateIndexStatement to use the full table path and include the
-- equality/inequality columns for easier identifcation.
------------------------------------------------------------------ */

SELECT  
 [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),  
 [Table] = [statement],
 [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_' 
  + sys.objects.name COLLATE DATABASE_DEFAULT 
  + '_' 
  + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
  + ' ON ' 
  + [statement] 
  + ' ( ' + IsNull(mid.equality_columns, '') 
  + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE 
   CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
  + mid.inequality_columns END + ' ) ' 
  + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END 
  + ';', 
 mid.equality_columns,
 mid.inequality_columns,
 mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs 
 INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
 INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN 
  (SELECT TOP (500) group_handle 
  FROM sys.dm_db_missing_index_group_stats WITH (nolock) 
  ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

FIND UNDER USED / UNUSED INDEXES
Since index is mainly used for reading, therefore tables that are written a lot but read rarely may be burdened by indexes. This script is useful for finding those tables. Within the result of this script, the "reads_per_write" is the ratio between the read and write to the table. Depending on the current performance, # of read/write, the index may or may not needed. Generally, Brent recommends to remove indexes where "read_per_write" is below 1.

SELECT 
o.name
, indexname=i.name
, i.index_id   
, reads=user_seeks + user_scans + user_lookups   
, writes =  user_updates   
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
 WHEN s.user_updates < 1 THEN 100
 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
  END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

FIND QUERIES THAT ARE MISSING INDEXES (using Plan Cache)
This is an EXPENSIVE query (not recommended to be run during peak-use-time), running against temporary cached plans.

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU 
, total_elapsed_time/execution_count AS AvgDuration 
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, execution_count 
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt 
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

-- read more and comment ...

Performance Tips (LINQ to SQL)

One part of the application that I am working on is performing poorly. According to the performance measurement that I get from using EQATEC profiler, it takes 3-4 seconds to load this page.

IIS / UI 
Since this is a web application, so I made sure all the IIS/non-application performance enhancements are in place:

• IIS compression are turned on
• Javascript files are combined and compressed
• CSS files are combined and compressed
• Images, scripts, and CSS files are cached
• Conforming to YUI rules & Google PageSpeed performance rules

Setting all those boost some performance in my application - not really that noticeable in normal usage. It cut down about 0.5 seconds or about 13% or my load time. So now we are in 2.8-3.5 seconds range. So what else?

-- read more and comment ...

Installing Visual Studio GDR R2

Visual Studio 2008, by default, will work with SQL 2005 database project, BUT NOT SQL 2008 based. Microsoft provided a GDR (General Distribution Release) to make VS 2008 work with SQL Server 2008. You can work the latest version of the GDR here.

Reading the description in the download link, the requirements are VS2008 SP1, nothing CTP or Beta or Power Tools install. So, I think my box met all those requirements, and I ran the install. To my surprise, it failed and it gives me this message: "Visual Studio Team System 2008 Database Edition GDR Does Not Apply or is blocked by another condition on your system. Please click the link below for more details.".

To which I was not sure of what to do. So by reading the forums and blog postings, I found out that the installer actually write a log file, located in your local data temp file dir. Mine (running on Win 7) is located in: "C:\Users\\AppData\Local\Temp". The files should be labeled like "Visual Studio Team System 2008 Database Edition GDR ... .html". If open one of those, you should see the log generated by the installed and you can detect where it failed.

By examining the log file, my installation failed because it was looking for the SP1 flag in the registry. So even though I have SP1 installed, it did not matter, the installer is just looking in to the flag in the registry and if it does not find it, it abort the installation.

Eventually I have to run the SP1 Preparation Tool and the SP1 itself.

After the SP1 re-installation, the registry setting for SP1 is properly marked as 1 (or true). So the next step is to run again the GDR installer - and it went successfully this time. All is happy now.

Update: Gert Drapers wrote a much more extensive blog post about this here. -- read more and comment ...

GeoDocs Reborn, version 8.0 Released!

After a long overdue, GeoDocs 8 was released by AWH on December 2009 (this blog post is late). Last month, GeoDocs revamped its website using the new version, new look and feel, and faster!

In a collaboration with Nationwide Children's Hospital, they became the first client to upgrade to the new version.

GeoDocs was also recognized as a semi-finalist for the 2009 TechColumbus Innovation Awards! GeoDocs was nominated in the category of 'Outstanding Product, Fewer than 50 Employees'.

So what makes GeoDocs 8 to be better than its predecessor?

There are a lot of reasons why GeoDocs 8 is better, as far as the technical aspects - here are some of them:

• Running on .NET framework 3.5 (GD 7 was running on .NET 1.1)
• The UI has been rebuilt from scratch using ASP.NET MVC
• SEO friendly URL
• AJAX integration with jQuery and jQuery UI
• Better and cleaner integration with Google Mini/Search Appliance
• Cleaner code base that enhance development experience

Now if you are a user, here are the benefits of GD 8:

• Much, much, much faster - and can be faster still depending on you configuration
• AJAX means better user experience:
  
  
  • Less screen refresh to load/reload data
  • Faster feedback in returning on-demand data
  • Nice and unobtrusive animations/cues for user actions
  • Faster and friendlier editing panel (Web View Editor)
  • More robust WYSIWYG editor
• SEO friendly URL, means nice looking URL and easily crawled by search engine
• If you are developing your own custom templates/look & feel - it will be much easier

This does not mean that we are removing all the good features and functionality that GD 7 has, GD 8 still has them, but better! GeoDocs since version 7 has boasted robust feature such as:

• Manage multiple website authors, provide workflow and security
• Help you manage your graphical brand and optimize navigation and layout
• Provide secure, granular access and information to defined users
• Customizable content types that will suit your needs
• Excellent and popular modules such as Form Builder & Calendar will continue to be supported and enhanced

GeoDocs is a product created and maintained by the Allen, Williams & Hughes Company, or AWH.

You can follow GeoDocs on twitter and on facebook.
-- read more and comment ...

SQL Shortcut Tip

In creating a SQL select statement to query some records from a table, it is pretty common to include WHERE clause - where you are limiting your selections by criterias specified.

Here is an example:

SELECT *
FROM Products
WHERE CategoryID = 5

The SQL statement above will return all Product records that are in Category 5.

Now, sometimes we want to use the condition only if it meets certain condition (like if the condition value is not empty or null).

So you SQL statement may look like this (assuming this is wrapped in a stored procedure) - where you want the stored procedure to return all Products if Category is not filtered:

ALTER PROCEDURE [dbo].[GetProducts] 
@CategoryID int = NULL 
AS

IF (@CategoryID IS NULL)
BEGIN   
SELECT *   
FROM dbo.Products
END
ELSE
BEGIN   
SELECT *   
FROM dbo.Products   
WHERE CategoryID = @CategoryID 
END  

Looks rather long isn't it? In LINQ this is a bit shorter, but still quite repetitive:

var data;
if (categoryId == 0)
   data = from row in db.Productselse
   data = from row in db.Products where CategoryID == categoryId 

So how can we short-cut this into shorter statements that is elegant and still do the job well?

Like this:

ALTER PROCEDURE [dbo].[GetProducts]
  @CategoryID int = NULL
AS   

SELECT *   
FROM dbo.Products   
WHERE (@CategoryID is NULL OR @CategoryID = 0) OR CategoryID = @CategoryID 

In LINQ:

var data = from row in db.Products
           where (CategoryID == categoryId  || categoryId == 0)

By including the possibility of null or zero (or any default condition) in the WHERE clause means that the condition will be computed as well to produce the results. Got it?

-- read more and comment ...