CXPACKET wait times and Degree of Parallelism

I have heard many times that the quickest way to improve the performance of a Sql Server instance is to throw more hardware at it. You would think this makes sense, but I have seen doubling the number of CPUs from (8 to 16) decreased the servers performance !

The reason is, under the default server settings, Sql Server will try to execute queries across all the CPUs in parallel. Depending on the type of the workload (OLTP or OLAP), the queries may take much longer to execute as splitting the query plan and combining the result again could take a lot loner than executing the query serially or with less number of CPUs.

Generally you will notice the parallelism issue with servers having more than 8 CPUs.
A quick way to check if your server is suffering from excessive parallelism is to look at the CXPACKET wait times.

This query will give you the top 10 wait types.

select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc

If you find CXPACKET value at the top of the list and you have more than 8 CPUs, you will probably benefit from lowering the degree of parallelism.
Also note that completely disabling parallelism (setting value to 1) may adversely affect large queries (OLAP). So you need to find the best value for your environment.
You can set the MAXDOP for individual queries which will override the server settings.

In order to change the degree of parallelism from the default (0) which means use all available CPUs to (say 4)

-- set to number of physical processors
EXEC sys.sp_configure N'max degree of parallelism', 4
GO
RECONFIGURE WITH OVERRIDE
GO

This will take effect immediately and no service restart is required.

Another option that is related to the degree of parallelism is the threshold (in seconds) that sql server will check before deciding to use parallelism.
The default value is 5 (i.e. Sql server will only use parallelism if the cost of executing a query serially is more than 5 seconds)

The following statement will modify this setting so that, sql server will only use parallelism for queries that take longer than 10 seconds to execute.

-- number of seconds elapsed
EXEC sys.sp_configure N'cost threshold for parallelism', N'10'
GO

The pictures below shows the affect of lowering MAXDOP on one of my servers on the processor queue length.

There is no rule of what value you should use, it all depends on your specific environment. But here are a few guidelines. Create a baseline of your server so that you can compare the performance of your server before and after the change.

upgrading sql server 2008 to SP2 breaks reports

After upgrading an instacne of sql server 2008 to SP2, I noticed that non of the reports from the management studio are accessable. including the reports that I frequently look at under the Management Data Warehouse.

The error message I was getting was;

The file 'Microsoft.ReportViewer.WinForms, version=9.0.0.0, Calture=neutral, publicKeyToken=b03xxxxxxx' cannot
be opened. Do you want to remove the reference to it from the Recent list?

In order to solve this, you need to download and install the Reporting Services Report Viewer 9.0 available from the following link;

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6ae0aa19-3e6c-474c-9d57-05b2347456b1


installation was very quick and did not require any restart.

Display different colour for different query windows in SSMS

When working with multiple query windows in SQL server management studio, It is useful to visually distinguish which server/ environment you are connected to.
Here is how you can configure SSMS to show a different colour for each environment.

In the Views menu click on Registered Servers "CTL-ALT-G"
Right click on the Local Server Groups under Database Engine and create some server groups e.g. (DEV, SIT , Demo, Prod)

Then right click on one of the new groups and create “New Server Registration”
In the “General” tab of New server Registration, configure the connection details
In the “Connection properties” tab, there is an option to “use custom color”. Tick the check box and pick your favourite colour
Once that is setup, every time you start a new query (right click on the server and select new Query) the query window will show the configured colour in the task bar.

Stored procedure slower than ad-hoc query

This morning I got a call from a developer saying that he is getting timeout on a call to a procedure. can you investigate please? We have orders that are piling up and not getting processed.! top priority

The developer sent me a windows form that i could use to test the issue. He was claiming that when calling the procedure, it takes 30 seconds then he gets a timeout but when using inline sql the query runs instantly.

Grabbed a coffee, fired the win form using the stored procedure and yes I got a timeout after around 30 seconds, then run the same thing using the inline query and it took a couple of milliseconds..himm

Executed the query that the procedure uses from management studio and it took a couple of seconds! I looked at the query plan, it was using an index seek and no issues there, no blocking too..

Sql server was using a different plan (in this case, a bad plan) for the procedure and a different plan when executing the ad-hoc query.

The issue happened to be that SQL Server creates an execution plan for a stored procedure when it is first called, and it tries to be cleaver by optimising it for the parameter that was used i the first call. However, this presents a problem if there is a large difference in the distribution of the number of records based on different parameters.


Let me explain:
If I execuete a procedure the first time and pass a parameter that returns 1 record from a table of 100 million record. Sql server creates a plan optimised for this parameter. If I then call the same procedure and pass a different parameter than returns 5000 records, assuming the first plan was a using an index seek and a key lookup, it will do the same for 5000 records which will be very inefficient.

There are a couple of ways round this;
1. to create local variables inside a stored procedure and assign the procedure variables to teh local variables. this way sql server will not be able to sniff the local variables.

2. use the OPTIMSE FOR () Hint


Create Procedure [dbo].[sp_GetObjectsByAttribute]
(
@AttrId int,
@Value varchar(128),
@Owner varchar(128)
)
As
SET nocount ON

SELECT TOP 500 o.ID, o.Handle, o.ClassID, v.AttributeID, o.Flags,o.Owner
FROM dbo.ObjectValues v
INNER JOIN dbo.[Objects] o on o.ID = v.ObjectID
WHERE
v.AttributeID = @AttrID
AND v.Value = @Value
AND o.Owner= @Owner
AND o.Flags & 1 = 0
OPTION (OPTIMIZE FOR UNKNOWN)
3. Force recompilation of the procedure evey time it is executed (not recommended)


This is a good post
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Unable to login to Navision after restoring database onto a new server

After restoring a Navision database from live server to the development server. it is not possible to login to the database using sa.

if you run the follwing sql statement, you will get
use [databaseName]
go
sp_change_users_login @action='Report'
go


UserName UserSID
dbo 0x90CEEB2B9F356D48B3EDFBFE736524C4

This shows that the username dbo is not associated with any logins. i.e. orphaned user. this happens because the logins are stored in the master database which is different from the master database on the development server.

changing the db owner of the database does not resolve this issue.
EXEC sp_changedbowner 'sa'

but fixed the orphaned user issue if the orphaned user is 'dbo'
so, after running EXEC sp_changedbowner 'sa', the database owner will be changed to 'sa' but you will still not be able to login to Navision with sa.

If you try to login using the Navision interface, you will get the folliwing message;

The user ID and password are invalid. Try again.

the only way i have found to solve this is to delete the users from the Navision [user} table. This is ok, if you are setting up navision on the develoment enviroment, but please be careful in deleting anything on produciton!!

so, in my case running the follwing command fixed the issue;
delete from [mydatabase].dbo.[User]

Why I prefer Log shipping over Mirroring

• I have more than one standby server (a local standby server within the same data centre and one in another country). Mirroring only allows for one standby server.
• I can re-use the standby server for reporting by setting the databases to restore with STANDBY. (You have to delay restoring the logs while the standby is accessed for reports otherwise the restore will fail). One way to do this is to allow reports to run during working hours say 09:00 – 17:00 and schedule the restore jobs to run after out of hours (17:00 – 09:00).
• In my case (15-30 minutes) downtime is acceptable failover time. If you are looking for less downtime, then you need to look at a combination Mirroring, Clustering and Log shipping.
• I don’t have reliable bandwidth to our DR site. This means when the link is down, the log files in the principle server will grow until the link is restored which is an issue as my databases generate huge transaction logs. In my case, all my transaction log backups are compressed as I use a 3rd party tool. I can even move them to another server if they get too many until the link to the DR site is restored.
• I have around 30 databases. If you have more than 10 databases, you should consider what impact Mirroring will have on performance as each database requires two threads. This is not the case with log shipping.
• I can decide when to apply the logs onto the standby server for instance I apply the logs after 20 minutes to catch any accidental/ malicious change to propagate to the standby). In the mirroring scenario, the change is instantly reflected in the mirrored database.
• I like keeping my databases in Bulk-Recovery mode to reduce the amount of data that goes into the transaction log for the minimally-logged operations. If you are looking at mirroring, you databases must be in Full recovery mode.
• I am limited to Synchronous mirroring as I don’t have the Enterprise Edition of Sql server. This is an issue for me as performance matters in my case (mainly due to the latency between the two sites).
• Also, to populate my data warehouse I extract from the standby server (so my ETL has virtually no impact on my production databases!). During the ETL process I don’t restore logs for 1 hour.
• Our application is legacy asp code that cannot automatically re-connect to the mirror in the case of failure (.net code can automatically re-connect in the case of mirroring failover). We still have to do manual changes for the failover to happen.

sp_executesql may cause slow perfomance

I stumbled across an interesting issue today when a simple query was taking too long to execute. The query was fired by a clinet using sp_executesql to one of my sql servers running Sql server 2000 SP4 Enterprise edition. Here is the query: exec sp_executesql N'SELECT Max(Id) FROM ExternalLinks WHERE ExtHandle=@ExtHandle',N'@ExtHandle nvarchar(14)', @ExtHandle = N'AT51988635-052'

and the IO stats were:

--Table 'ExternalLinks'. Scan count 2, logical reads 4804, physical reads 0, read-ahead reads 0. The query was doing 2 scans and 4804 logical reads. When i tried to exeute the query using query analyser without the sp_executesql,

SELECT Max(Id) FROM ExternalLinks WHERE ExtHandle='AT51988635-052' i got : --Table 'ExternalLinks'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0. After investigating, i figured out that the client was using sp_executesql which was passing a parameter as Nvarchar as oppesed to Varchar (the datatype of the table is varchar). This was causing the query engine to convert the data before comparing. This may not be noticable on a small table, but this will make a big impact on large tables. The execution plan for the query that was using sp_executesql is shown below which uses a clustered Scan rather than a seek as it needs to convert all the values before it can compare the values.

The argument shows the convert function.

The second query (without the sp_executesql) uses an index seek which is much more ifficient.

Just to clarify that this is not a shortcoming of sp_executesql, it is rather the client that decided to use Nvarchar for the field rather than varchar. I get the same results if i use the correct data type with sp_executesql.

** If you are using Nhibernate mapping file, you can use type="AnsiString" to force enhibernate to use varchar data type.