Very Poor Performance - Identical Databases Yield Different Performance

Question

Very Poor Performance - Identical Databases Yield Different Performance

BrianAtSQLCentral

SSC Veteran

Points: 204
More actions
June 22, 2006 at 10:33 am

#114027

Hello Everyone,
I have a very complex performance issue with our production database. Here's the scenario. We have a production webserver server and a development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with a particular query. It would take approximately 22 seconds to return 100 rows, thats about 0.22 seconds per row. Note: I ran the query in single user mode. So I tested the query on the Development server by taking a backup (.dmp) of the database and moving it onto the dev server. I ran the same query and found that it ran in less than a second.
I took a look at the query execution plan and I found that they we're the exact same in both cases.
Then I took a look at the various index's, and again I found no differences in the table indices.
If both databases are identical, I'm assumeing that the issue is related to some external hardware issue like: disk space, memory etc. Or could it be OS software related issues, like service packs, SQL Server configuations etc.
Here's what I've done to rule out some obvious hardware issues on the prod server:
1. Moved all extraneous files to a secondary harddrive to free up space on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of magnitude difference and I can't explain it. To me its is a hardware or operating system related issue.
Any Ideas would help me greatly!
Thanks,
Brian T

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

Try looking at perfmon counters on the servers (if you can get a quiet time to do it on the prod server) to check which resource is affected.

Other than that, at the moment I can only suggest breaking down the query (removing items from the 'select' list, and tables from the 'from' clause) to try and narrow down which object(s) are implicated in the performance differential.

As you suggest, server configurations would be worth looking into. Check server options on both servers - something like collation conversions, or difference in ANSI settings might cause overhead in execution without showing up on the plan.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>I took a look at the query execution plan and I found that they we're the exact same in both cases

When you "took a look", was this the graphical execution plan displayed in query analyser ?

Is it possible you missed the icon modification for parallelism on 1 of the plans ?

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 3

Hi guys

Thanks for the promt reply.

Sql server is set to dynamically configure its own memeory. It has access to all 2gb of memeory. Generally sqlsevr.exe takes about 700mb's, and at anytime, the system has about 800mb of available physical memory.

The server itself is not overloaded with processes. Generally it will the processors will operate at under 5% on a daily basis. When I execute a specific query, it will jump to about 25%. I tried to run perfmon, a while ago. I'm not very familar with it so I couldn't use the information to find any leads. Any ideas what counters I should use? Do you think there could be some process locks going on?

As far as the execution plan goes, I did use the graphical representation. How do you use the text based one? If I knew how, I could post it here so that we can analize this further.

Your help is very much appreciated. Thanks

Brian T

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

Add this before the SQL statement to collect textual showplan:

Set showplan_text on

go

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 5

Here is the queryplan. This query is very complex as you can see. Is there a way you also display performance statistics?

|--Sort(ORDER BY[Union1170] ASC))

|--Concatenation

|--Compute Scalar(DEFINE[Expr1055]=Convert(Convert([dbo].[fnDEVICE_UNIT_CONVERT](Convert(If (isnumeric([T_EQUIPMENT_LOG_DATA].[READ_VALUE])=1) then If (charindex(',', [T_EQUIPMENT_LOG_DATA].[READ_VALUE], NULL)>0) then 0.000000000 else Co

| |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| |--Nested Loops(Inner Join, OUTER REFERENCES[Union1020]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES[Union1020]))

| | | |--Sort(DISTINCT ORDER BY[Union1020] ASC, [Union1021] ASC, [Union1022] ASC, [Union1023] ASC, [Union1024] ASC, [Union1025] ASC, [Union1026] ASC, [Union1027] ASC, [Union1028] ASC, [Union1029] ASC, [Union1030] ASC, [Unio

| | | | |--Compute Scalar(DEFINE[Expr1010]=Convert(If (isnumeric([T_ALARM_RULE].[ALARM_RULE_VALUE])=0) then NULL else [T_ALARM_RULE].[ALARM_RULE_VALUE])))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_ALARM_RULE].[EQUIP_VARIABLE_ID]))

| | | | | | | |--Clustered Index Scan(OBJECT[AFTI_PROD].[dbo].[T_ALARM_RULE].[PK_T_ALARM_RULE]), WHERE([T_ALARM_RULE].[EQUIP_VARIABLE_ID]<>NULL AND [T_ALARM_RULE].[IS_ACTIVE]<>0) AND [T_ALARM_RULE].[

| | | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[T_ALARM_RULE].[EQUIP_VARIABLE_ID]) ORD

| | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_V

| | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK[T_EQUIPMENT].[EQUIPMENT_ID]=[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]) ORDERED FORWARD)

| | | | |--Compute Scalar(DEFINE[Expr1019]=Convert(If (isnumeric([T_ALARM_RULE].[ALARM_RULE_VALUE])=0) then NULL else [T_ALARM_RULE].[ALARM_RULE_VALUE])))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_ALARM_RULE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | | |--Clustered Index Scan(OBJECT[AFTI_PROD].[dbo].[T_ALARM_RULE].[PK_T_ALARM_RULE]), WHERE([T_ALARM_RULE].[EQUIP_VARIABLE_ID]=NULL AND [T_ALARM_RULE].[IS_ACTIVE]<>0) AND [T_ALARM_RULE].[A

| | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_ALARM_RULE].[EQUIP_TYPE_VARI

| | | | | |--Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UK_EQUIPMENT_VARIABLE_1]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID])

| | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK[T_EQUIPMENT].[EQUIPMENT_ID]=[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]) ORDERED FORWARD)

| | | |--Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[T_EQUIPMENT_LOG_DATA8]), SEEK[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]=[Union1020] AND [T_EQUIPMENT_LOG_DATA].[CREATE_DATE] > [@ALARM_FROM_DATE]) ORDERE

| | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[Union1020]) ORDERED FORWARD)

| |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]) ORDERED FORWARD)

|--Compute Scalar(DEFINE[T_EQUIPMENT_LOG_DATA].[READ_VALUE]=[T_EQUIPMENT_LOG_DATA].[READ_VALUE]))

| |--Compute Scalar(DEFINE[Expr1113]=If ([T_EQUIPMENT_LOG_DATA].[READ_VALUE]=[Union1083]) then 'ALARM' else 'Normal'))

| |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| |--Nested Loops(Inner Join, OUTER REFERENCES[Union1077]) WITH PREFETCH)

| | |--Nested Loops(Inner Join, OUTER REFERENCES[Union1077]) WITH PREFETCH)

| | | |--Sort(DISTINCT ORDER BY[Union1077] ASC, [Union1078] ASC, [Union1079] ASC, [Union1080] ASC, [Union1081] ASC, [Union1082] ASC, [Union1083] ASC, [Union1084] ASC, [Union1085] ASC, [Union1086] ASC, [Union1087] ASC,

| | | | |--Compute Scalar(DEFINE[Expr1067]=Convert(If (isnumeric([T_ALARM_RULE].[ALARM_RULE_VALUE])=0) then NULL else [T_ALARM_RULE].[ALARM_RULE_VALUE])))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_ALARM_RULE].[EQUIP_VARIABLE_ID]) WITH PREFETCH)

| | | | | | | |--Clustered Index Scan(OBJECT[AFTI_PROD].[dbo].[T_ALARM_RULE].[PK_T_ALARM_RULE]), WHERE([T_ALARM_RULE].[EQUIP_VARIABLE_ID]<>NULL AND [T_ALARM_RULE].[IS_ACTIVE]<>0) AND ([T_ALARM_R

| | | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[T_ALARM_RULE].[EQUIP_VARIABLE_ID]

| | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIPMENT_VARIABLE].[EQUIP_T

| | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK[T_EQUIPMENT].[EQUIPMENT_ID]=[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]) ORDERED FORWARD)

| | | | |--Compute Scalar(DEFINE[Expr1076]=Convert(If (isnumeric([T_ALARM_RULE].[ALARM_RULE_VALUE])=0) then NULL else [T_ALARM_RULE].[ALARM_RULE_VALUE])))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[T_ALARM_RULE].[EQUIP_TYPE_VARIABLE_ID]))

| | | | | | |--Clustered Index Scan(OBJECT[AFTI_PROD].[dbo].[T_ALARM_RULE].[PK_T_ALARM_RULE]), WHERE([T_ALARM_RULE].[EQUIP_VARIABLE_ID]=NULL AND [T_ALARM_RULE].[IS_ACTIVE]<>0) AND ([T_ALARM_RU

| | | | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_ALARM_RULE].[EQUIP_TYPE

| | | | | |--Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UK_EQUIPMENT_VARIABLE_1]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE

| | | | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK[T_EQUIPMENT].[EQUIPMENT_ID]=[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]) ORDERED FORWARD)

| | | |--Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[T_EQUIPMENT_LOG_DATA8]), SEEK[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]=[Union1077] AND [T_EQUIPMENT_LOG_DATA].[CREATE_DATE] > [@ALARM_FROM_DATE]) O

| | |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[Union1077]) ORDERED FORWARD)

| |--Clustered Index Seek(OBJECT[AFTI_PROD].[dbo].[T_EQUIP_TYPE_VARIABLE].[PK_T_EQUIP_TYPE_VARIABLE]), SEEK[T_EQUIP_TYPE_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]) ORDERED FORWARD)

|--Compute Scalar(DEFINE[Expr1143]=Convert([fnGET_ALARM_MULTI_VALUES].[ALARM_RULE_TYPE]), [Expr1144]=Convert([fnGET_ALARM_MULTI_VALUES].[NOTIFY_OWNER]), [Expr1145]=Convert([fnGET_ALARM_MULTI_VALUES].[NOTIFY_OTHER]), [Expr1146]=Convert(Co

|--Table Scan(OBJECTfnGET_ALARM_MULTI_VALUES))

stax68 SSChampion Points: 11711 More actions · Answer 6

Use the SQL Server-specific counters. Perfmon is mostly meant for broad-brush analysis of performance as a whole. If you can get exclusive access to SQL Server on both servers while you run the corresponding statements, differences in performance ought to be detectable though...

For one of many guides to using perfmon for SQl Server diagnostics, see:

http://www.windowsitlibrary.com/Content/77/15/1.html

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 7

Have you used a text-comparison tool like textpad to check for differences in the detail of the execution plans?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 8

Hi Tim, thank you for your help.

I've checked both execution plans and they are the exact same.

I'm going to try to use perfmon to see if I can find anything else. Any suggested channels that I should have a look at?

Any other ideas??

Thanks!

Brian

David Toft Valued Member Points: 60 More actions · Answer 9

You could try following the instructions on this site to perform a performance audit on the server. It tells you what to check and how to interpret what you find:

http://www.sql-server-performance.com/sql_server_performance_audit.asp

Dave.

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 10

Hello everyone,

I have an update to this thread on some of the progress I've made since.

I manged to look at the text based actual query execution plan on the different servers and I found some very minute differences in the plan. These differences are in the following format: Nested Loops(Inner Join, OURTER REFERENCES: (TABLE.COLUMN)). Now on both servers the table is the same, but the difference between both servers is in the column value. There is also a clustered index seek difference. Here is the difference: Clustered Index Seek(OBJECT(___), SEEK(____) ORDERED FORWARD) both of the ____ objects are different in prod rather then dev.

So I made sure that the index's on both servers we're the EXACT same, and that has been verified. So here are my questions:

1. What else affects query execution plans?

2. How can I find out if the difference in the execution plans, results in the difference in execution time?

Thanks for the link Dave, I will go through the doc as soon as I can.

Any help would be greatly appreciated!

Thanks,

Brian

stax68 SSChampion Points: 11711 More actions · Answer 11

Impossible to say what's happening without seeing the differences between the two plans in more detail.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 12

Hello everyone,

heres an update on what I've been doing.

I've updated the statistics on the prod server and verified that the (text) execution plan is identical to dev. But I still have bad performance from the prod server.

I also check out table index fragmentation, and but all tables have 0% Logical Scan Fragmentation.

I also tried using

SET STATISTICS TIME Off

SET STATISTICS IO Off

this is what it returned:

Prod:

Table '#219FF574'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

Table 'T_1'. Scan count 11, logical reads 22, physical reads 0, read-ahead reads 0.

Table 'T_2. Scan count 14, logical reads 35, physical reads 0, read-ahead reads 0.

Table 'T_3. Scan count 10, logical reads 45, physical reads 0, read-ahead reads 0.

Table 'T_4. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0.

Table 'T_5. Scan count 4, logical reads 16, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 4656 ms, elapsed time = 4664 ms.

SQL Server Execution Times:

CPU time = 4656 ms, elapsed time = 4665 ms.

Dev:

Table '#6FFB1919'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

Table 'T_1. Scan count 11, logical reads 22, physical reads 0, read-ahead reads 0.

Table 'T_2. Scan count 14, logical reads 35, physical reads 0, read-ahead reads 0.

Table 'T_3. Scan count 10, logical reads 45, physical reads 0, read-ahead reads 0.

Table 'T_4. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0.

Table 'T_5. Scan count 4, logical reads 16, physical reads 0, read-ahead reads 0.

Any other suggestions guys? Again thanks for all your help.

Brian

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 13

New Update:

It looks like getting in a secondary server is on hold at the moment, so I won't be getting my hands on the prod server any time soon. I've found some other routes which I can possible try this weekend.

1. Install a new instance of SQL Server. The new instance of SQL server will create a new copy of the SQL engine, and system, msdb, model (etc) dbs. So if I find that the query runs faster on the new instance then I know that the problem is related to the difference in the system dbs. Our original attempt was to reinstall SQL Server all together, but this might be too risky, and it would required a few hours of down time. I do not want to do a reinstall of SQL server, unless we we're sure 100% that we could be back up in an hour or two. I also would like atleast a standby server to fall back on as well.

We also considered installing a virtual pc on the server, with a new os and a new install of sql server. This would completely eliminate any software related issues, and I would be sure that it was a hardware problem.

Any ideas?

Thanks,

Brian

stax68 SSChampion Points: 11711 More actions · Answer 14

Maybe the production environment is using a suboptimal stored plan. You can get rid of all plans with DBCC FREEPROCCACHE, but that could cause temporary performance problems on the prod server. I think DROPping and reCREATEing the stored proc would cause its plans to be dropped from the cache. When the proc is next run, a fresh plan will be generated. If you do the same on both servers and then run the proc with the same parameters on both, you should be able (not guaranteed I suppose) to get them to use the same plan, if the data, stats, indexes, filegroups etc. are identical. Make sure you save a copy of the good plan first, in case this causes both servers to use a bad plan.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant