Peculiar Performance Issue. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Peculiar Performance Issue.

Author

Topic

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-12-01 : 04:41:06

Hi all,

We have a issue with the performance in SQL server database.

Scenario & Issue:
We have delivered a .net application to our client. This application is installed in newly built windows 2003 server.

The client is facing performance issues with the application. When compared with the performance in the development server , the performance of the production server is very poor.

Even when we execute the stored procedures in the backend, the performance is poor in the production server.

Example: A stored procedure that takes 16 seconds in the development server takes 17 minutes for the same parameters. The time remains the same even for HOT execution.

System Info:

Database Version - SQL Server 2005
Database Size - 120 plus GB
OS Platform - Windows 2003
Database Load - 50 users
CPUs - 4
RAM - 8 GB

The OS is Clustered ( failover clustering ).

Points to Note:

1.There is a huge table with 250 million rows ( this table itself takes upto 60 GB )

2.The huge table is partitioned ( SQL server 2005 table partitioning ) and placed in 20 different filegroups (.mdfs).

3.The .mdf's are placed in a SAN and .ldf is in local HD

4.Dynamic queries are used at few instances for performance benefits.

Questions:

1. Any thoughts on why this kind of performance issue arises ?

2. The client DBA wants us to clear the data and stored procedure cache before executing the stored procedure and test the performance.

Will this be would be the case in production scenario ?

3. Will the performance change based on the input parameters ?

4. The client DBA also have stated that a report server that pings the production database server is the cause for frequent clearing of the SQL Server cache.

When does the SQL Server database actually clears the cache memory? Is there any way to control it?

Any help would be highly appreciated.

Thanks & Regards,

Hariarul

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-01 : 08:38:45

First thing I would do is compare the execution plans for the same call (same parameters) between dev and production. They will likely be different.
If the execution plans are different:
Are you using the same sql server version between environments?
Do you have identical ddl between all tables involved? (check for differences in constraints, indexes, etc)
If everything is the same so far you may need some sort of maintainance like "update statistics" on all the tables involved.
Check for blocking in the production environment - (sp_who2 active) - concurrent operations may be colliding.

Another direction to check is the general performance on the production db server. Does everything perform poorly or just your SP? If so that will lead down a different path of questions.

(some) Answers:
2. shouldn't be the case in normal production scenario. I think the client dba just wants to compare apples to apples - everything unchached for testing.

3. Certainly could - depends on the SP specifics.

4. I'm not sure why "pinging the db server" should clear cache. Ideally you want to leave the cache nice and full so it can be used.

Be One with the Optimizer
TG

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-12-01 : 11:08:32

Thanks for your response TG. I forgot to mention that the execution plans remains the same except for few cost % difference for the same event.

Example : An index scan takes 27% in the dev server and the same index scan takes 30 % in the production server.

And for your question , Does everything perform poorly or just your SP?

Everything performs poorly comparetively in the production server.

Any other thoughts ?

Thanks again,

Hariarul

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-01 : 13:35:07

quote:
Originally posted by Hariarul

Hi all,

We have a issue with the performance in SQL server database.

Scenario & Issue:
We have delivered a .net application to our client. This application is installed in newly built windows 2003 server.

The client is facing performance issues with the application. When compared with the performance in the development server , the performance of the production server is very poor.

Even when we execute the stored procedures in the backend, the performance is poor in the production server.

Example: A stored procedure that takes 16 seconds in the development server takes 17 minutes for the same parameters. The time remains the same even for HOT execution.

System Info:

Database Version - SQL Server 2005
Database Size - 120 plus GB
OS Platform - Windows 2003
Database Load - 50 users
CPUs - 4
RAM - 8 GB

The OS is Clustered ( failover clustering ).

Points to Note:

1.There is a huge table with 250 million rows ( this table itself takes upto 60 GB )

2.The huge table is partitioned ( SQL server 2005 table partitioning ) and placed in 20 different filegroups (.mdfs).

3.The .mdf's are placed in a SAN and .ldf is in local HD

4.Dynamic queries are used at few instances for performance benefits.

Questions:

1. Any thoughts on why this kind of performance issue arises ?

2. The client DBA wants us to clear the data and stored procedure cache before executing the stored procedure and test the performance.

Will this be would be the case in production scenario ?

3. Will the performance change based on the input parameters ?

4. The client DBA also have stated that a report server that pings the production database server is the cause for frequent clearing of the SQL Server cache.

When does the SQL Server database actually clears the cache memory? Is there any way to control it?

Any help would be highly appreciated.

Thanks & Regards,

Hariarul

(1) When was the last time the tables were reindexed? Try doing an update stats with full scan on all tables and see if makes a difference. If, reindexing will help.

(2) After you clear the cache, the first execution of the proc will definetely be slower because it has to parse the proc and create the query plan first. Subsequent executions will use the direct query plan directly, so they would be faster.

(3) Definetely. Do you have a lot of IF loops in the proc? Does the query being executed change dramatically with different parameters?

(4) SQL Server gets to cache if its memory starved. If your memory utilization is high consistently high and you have done everything from the application side, perhaps adding more RAM might help.

Are we talking about a handful of procs or entire application? Were the procs developed in 2005 version or 2000 version? Not that they will be totally different, but the 2005 engine is different from 2000.

To pick the low hanging fruit first, I'd try with updating the stats on all tables or reindex + update stats.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-12-01 : 16:40:08

Question 1.
Hm. If you have 250M rows, I would say, *ANY* Index/Table scan is fatal for OLTP (but may be ok for OLAP). I can not imagine an index scan of 250M rows performed faster then 2-3 minutes.

Question 4.
Looks like your client is running some reports on huge data. There reports read a lot of information and repopulate the SQL server cache with the underlying data for these reports. Hence ->

Question 2. as a logical result your client needs to be sure that your sp exec time is acceptable even after the 'COLD' run: when server is restarted and cache is empty, or after running these reports, when cache is populated with the data uselesss for your query

For me, 2 is a logical result of 4, and looks like your client's DBA is highly quelified (70% of DBAs are even not avare of DBCC DROPCLEANBUFFERS :) )

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-01 : 18:26:04

quote:
Originally posted by Hariarul

[And for your question , Does everything perform poorly or just your SP?
Everything performs poorly comparetively in the production server.

This is sounding like it could be a system, hardware, or configuration issue.

Just as a "stab in the dark" try taking a statement that is measurably slower on the production server and run it using only 1 processor by using: option (maxdop 1)

Be One with the Optimizer
TG

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 00:40:26

How much memeory used by sql?

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-12-02 : 09:14:33

The memory (Page file) usage goes and stops at the maximum level
(8GB) always.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-12-02 : 10:12:08

Page file?
AWE memory is not paged at all...
Use perfmon to check SQL server buffer pool memory

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-02 : 12:42:34

SQL Server should not be using Page file. It should be capped at around (Max memory - 8gb for the OS).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 16:15:54

And memory is different from page file.

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-12-04 : 04:31:43

How do it find the memory used by SQL Server in a system ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-04 : 22:26:53

Check it in perfmon.