SQL Server 2005 ODBC Driver performed slow

Last Post 03 Dec 2010 03:39 PM by rm. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
shazadfakhir
New Member
New Member

--
30 Nov 2010 02:52 AM

Hi,

My name is Shazad. I am an experienced database developer for writing queries and Stored Procedures for Crystal reports.

But currently my company moves me to perform DBA operations. Situation was going well until I got some errors on our Production Server’s when running queries is some reports, the error is “Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” I have noticed that this error would be generated whenever some other users are updating/viewing the records who are also appeared in the selected query/Stored Procedure. The query was also getting slow before throwing this error.

I had tried Google for resolution of this error and found solution that use “WITH (NOLOCK)” hints on base tables and views. Also add the command “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” in my stored procedures. This would resolved the deadlock error.

But now I have observed that the queries/SPs who generate that error are working fine on SQL Server Management Studio. But when I call these queries/SPs on my crystal reports using ODBC connection on the same situation when other users are updating records, the query although won’t throw any deadlock error take very long time to generate the Result Set (the query returned the result in 20 second on Mgmt Studio while same query with same parameters retuned the same Result Set in 25 minutes). This also happens when I create a linked server of our Prod Server and execute the SP from that linked server.

I have also created DB maintenance tasks which I run periodically.

I have tried both ODBC drivers for SQL Server i.e. the driver shipped with windows and the SQL Native Client driver.

Can anyone tell that why it is doing so. Is there any extra configuration required for ODBC connection?

My Production Server Specs are given below.

Hardware Detail

HP ProLiant DL580 G4 Server

• No. of Processors : 4
• No. of Core / Processors : 2 (Dual Core)
• Memory : 8GB
• No. of Cards in slots : None
• No. of Hard Disks : 2 (72GB 15K RPM Each)

Operating System Details

• Operating System : Windows 2008 Server Enterprise Edition R1 Service Pack 1.
• Operating System Type : 64-bit Operating System.

SQL Server Details

• Operating System : SQL Server 2005 Server Enterprise Edition Service Pack 2.
• Operating System Type : 64-bit Operating System.

rm
New Member
New Member

--
30 Nov 2010 04:59 AM
Do you run report on client machine? Checked network traffic?
gunneyk
New Member
New Member

--
30 Nov 2010 06:44 AM
First off I find it very funny that you are running Enterprise editions of Windows and SQL Server and yet you only have 8GB or memory and 2 disk drives. But oh well. Anyway you need to look at the query plans for each and find out what is different. The ODBC driver may be using a different set of options such as SET ANSI NULLS, QUOTED IDENTIFIERS etc. and may be turning IMPLICIT TRANSACTIONS on as well. Check to see how it connects and what options it uses or commands it issues to SQL Server. You can see this with Profiler.
shazadfakhir
New Member
New Member

--
30 Nov 2010 09:00 AM
Yes I had run this report on client machine and etwork was ok.
and at the same time I had tested the same stored procedure on management studio and crysal reports.
rm
New Member
New Member

--
30 Nov 2010 09:31 AM
Did you check set options in odbc connection as gunneyk said? They do affect performance based on our experience.
shazadfakhir
New Member
New Member

--
30 Nov 2010 08:17 PM
Here is my current ODBC configuration:


Data Source Name: airport2020
Data Source Description:
Server: 10.10.0.24
Database: Airport2020_PK
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: Yes
Query Log File: C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\QUERY.LOG
Query Log Time: 30000
Log Driver Statistics: No
Use Integrated Security: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No



How do I check that implicit transaction is off or on and how I turned it off if it is already on?
shazadfakhir
New Member
New Member

--
30 Nov 2010 09:21 PM
Here is the connection settings extracted from SQL Profiler.

set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed


I have added "set transaction isolation level read uncommitted" in my stored procedure so it should read uncommited records also.


rm
New Member
New Member

--
01 Dec 2010 04:47 AM
Are they same as ssms settings?
shazadfakhir
New Member
New Member

--
01 Dec 2010 07:50 PM
These settings are same as SSMS settings. However I have read in an article and found that this situation is due to SQL Server Shared locks on table and resulted in transactional deadlocks. The query was hold because of deadlock and show result when deadlock was removed.
So I want that SQL Server should not use deadlock mechanism. I have read that from SQL Server 2005 , they give an SnapShot option Click here to read the article from this I conclude that we have to apply "ALLOW_SNAPSHOT_ISOLATION " and "READ_COMMITTED_SNAPSHOT" options on our Database. and use "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" in stored procedure rather than "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED".

Please give your recommendations on applying this strategy.
rm
New Member
New Member

--
02 Dec 2010 05:00 AM
May work but have to watch tempdb, will fill up disk if don't handle connections properly.
shazadfakhir
New Member
New Member

--
02 Dec 2010 07:53 PM
I have read the article about monitoring tempDB's space Click here to read the article a but don't know how to free the space in tempDB.
rm
New Member
New Member

--
03 Dec 2010 03:39 PM
Have close read session properly to release versioning space in tempdb.
You are not authorized to post a reply.

Acceptable Use Policy