Pythian Group

If you ever used SQL server Database Engine Tuning Advisor then you know you have few options as a source of analysis workload:

- SQL server Script file *.sql

Includes a query or set of queries targeting one or more databases. Tuning advisor will evaluate the script against target databases(s) and attempt to provide any recommendations.

- Trace file using SQL server profiler *.trc

You can use SQL server Profiler to capture different events happening at the instance and record these events on disk.

You can use the GUI to capture the events and watch them in real time then LATER save that trace to a file on disk or use server-side traces and the trace will automatically be saved to the file mentioned in the trace definition.

Read the rest of this entry . . .

If you do any performance tuning , and everyone do some day, then you’ll come to a point where you want to know if your tables have sufficient indexes to serve the queries fast.

Starting with SQL server 2005 , engine tracks indexes usage , through query optimizer, and can now determine if a query can benefit from adding indexes; this information can be identified using few Dynamic Management Views (DMV) including

sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns

You can determine an ***estimate*** of the number of missing indexes per database by running following query

SELECT DB_NAME(database_id) Database_name
, count(*) No_Missing_indexes
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY count(*) DESC;

One important fact is that missing Indexes DMVs can track a maximum of 500 indexes so if you have many databases with many active tables then there’s a chance not all of the info will be present.

I’ve seen plenty are useful queries about creating statements to create these missing indexes but they just give vague names to indexes ,based on sys.dm_db_missing_index_groups fields.

Since it’s a very good practice to standardize your objects’ names , the following query will give names to indexes in following format Read the rest of this entry . . .

I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  loginame , nt_username
order by count(*) desc

This showed a mix of windows domain accounts (those with values in column nt_username, e.g: domain\user) beside SQL authentication accounts (those with column nt_username empty, e.g: “sa”).

In order to reduce the number of times that new connections must be opened, applications may use connection pooling. This was clearly not the case here, and all these connections resulted in what is known as “pool fragmentation”.

So what’s connection pooling? how does it work, what can cause pool fragmentation and how can we avoid/reduce it?

Read the rest of this entry . . .