May 20, 2011 at 1:32 pm
We are facing performance issue with our one of instance of SQL server 2005.
Configuration is
SQL servers 2005 Ent Edition (64-bit)
Total Physical Memory: 32 GB (No chance of increasing the Memory)
MAX memory configured is 30 GB
Database size: 6,059GB (or) 6TB
SQL Server: Buffer Manager: Buffer cache hit ratio : 99.11
SQL Server: Buffer Manager: Page Life Expectancy : below 100-150(wired)
This a Dataware house system, I mean it is only readonly server for selects and genrating reports.
When I open and see almost Activity Monitor all the SPID will be in SUSPENDED Status. I dont see much blocking. But some times same porcess blockes by it self.
Any body can help me how to proceede from here. How to troubleshoot?
Do I need to create indexs? if yes, how?
If I look at the Performance Dash Board Main: it shows a alert saying 'System performance me be degraded because of exessive waits happening on the server. Click on a wait category data point in the chart below to investigate further'
When click on the waits i got a Cumulative wait time by wait category.
How do I troubleshoot from here.
May 20, 2011 at 1:39 pm
And the wait types are ????????
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 1:46 pm
Below shows the Wait Category:
Wait CategoryNumber of WaitsWait Time (sec)% Wait Time
Parallelism277458685332375283.6260.11%
Number of WaitsWait Time (sec) %Wait timeMax Wait Time (ms) Avg Wait Time (ms)
CXPACKET 277458685332375283.62 100.00%140952644 11.7
Sleep44643310818564300.64915.90%
Other17813168525030005.4979.34%
Buffer IO1166954825021800.9769.32%
Scheduler Yield6968961401580982.2882.94%
Latch35644619814795.7641.51%
Memory256566 196368.4260.36%
Lock3627 124354.6640.23%
Logging1164462987515.9980.16%
Network IO9837054 52910.3280.10%
Buffer Latch20057602513973.9590.03%
Transaction4 1.232 0.00%
Compilation65 0.015 0.00%
May 20, 2011 at 1:48 pm
Ok, so are the queries actually slow, or are you just concerned about the waits?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 1:53 pm
Both.
They are reporting slowness.
And I am Thinking the slowness is because of waits ?
I am not sure how to toubleshoot from here?
For better better performance what Should I must do...how do I touble shoot.
and How can I increase the
SQL Server: Buffer Manager: Page Life Expectancy : above 300
May 20, 2011 at 2:47 pm
greatly appreciate any suggestions on this issue ?
May 20, 2011 at 3:01 pm
1) What does the following script show when the server is running slowly? The runnable task count column shows the number of tasks for each processor waiting on CPU.
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
(This is a script I got - probably from this web site. I did not develop it myself)
2) What is the CPU utilization when the server is slow - use perfmon?
3) What is the operating system paging rate when the system is slow - use perfmon?
Chris
May 23, 2011 at 1:21 am
Your PAGE LIFE EXPECTANCY value is too low,its not lower than 3000,you should have to increase the RAM
1-How much Physical and Logical Processors ?
2-Total Drives not Partitions ?
3-Tempdb location ?
4-SQL Server Service Pack ?
5-SQL Server Binaries Files Location ?
6-Database Files location ?
For Disk IOs
=========
use [your database name]
go
select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc
For Queries
========
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
order by cpu desc
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 23, 2011 at 4:18 am
If you know for sure which queries are slow, take a look at the execution plans and see what they're doing. Tune the queries. If you're not sure which queries are slow you need to identify them. Gail has an excellent article [/url]over on Simple-Talk that can get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2011 at 7:48 am
@ croberts
Thanks For your replay
When I run the query it gives:
scheduler_idcurrent_tasks_countrunnable_tasks_count
0 14 1
1 140
2110
3101
4110
5141
6122
7141
May 23, 2011 at 8:18 am
The last column shows that there were not many threads waiting on a CPU the the moment you ran the query. I was wondering if there were a lot of queries using parallelism (multiple processors). That would have shown up as higher numbers in the last column.
Unrelated to the above, you may want to check out the following:
What do you have the max server memory set to in the SQL Server configuration? SQL Server will not use more than this amount, even though more is available. This could result in low page life expectancy.
Chris
May 23, 2011 at 8:19 am
@ Syed Jahanzaib Bin hassan
Thanks for your replay, It is some what helpful,
there is no chance of increasing the memory on the server.
drive Totalsize
C 279GB--> OS and SQL Sever, Binaries and patchs.
E 299GB--> one single Data
G 250GB--> Sytem Defult Database and aslo Temp DB.
H 1.76TB--> data file and back up files
I 2TB --> One single large database in to 6 datafile
J 420GB --> logs
K 2TB --> backups
L 1TB -->backups
M 2TB --> data
FOR SQL Queries:
Got the sql queries and max CPU usage.
May 23, 2011 at 8:23 am
@ Grant Fritchey
Thanks for the reply.
For sure the SQL Queries are slow. How do I start over and troubleshoot.
May 23, 2011 at 10:05 am
Danzz (5/23/2011)
@ Grant FritcheyThanks for the reply.
For sure the SQL Queries are slow. How do I start over and troubleshoot.
Tuning queries is a huge topic. If you start with Gail's article you'll know which queries are the slowest. Then you have to identify what those queries are doing that is slow and modify them so it isn't slow any more. It's hard to say more than that without knowing more. You might want to get a copy of my book on Query Performance Tuning. It's a big topic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2011 at 12:02 pm
Grant Fritchey (5/23/2011)
Danzz (5/23/2011)
@ Grant FritcheyThanks for the reply.
For sure the SQL Queries are slow. How do I start over and troubleshoot.
Tuning queries is a huge topic. If you start with Gail's article you'll know which queries are the slowest. Then you have to identify what those queries are doing that is slow and modify them so it isn't slow any more. It's hard to say more than that without knowing more. You might want to get a copy of my book on Query Performance Tuning. It's a big topic.
Or, as I suggested to you via PM, see if management will get a consultant in. Query tuning is not something you can learn in a day or two, especially if you're under pressure and the problem is critical and urgent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply