Performance issue

Question

Performance issue

Viewing 7 posts - 16 through 21 (of 21 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

sqlnsg (1/8/2010)
Thanks a lot for all your inputs and feedback. Ok basically the issue here is SQl dose'nt respond to client request in particulat , its diffcult to capture the trace as its a heavy transactions , Client is reporting slow response , some sometime timeout issue , . I am attaching fe of the perfmon counter snapshot for your reference . And yester we received the following I/O error as well.
SQL Server has encountered 32 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [G:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000700. The offset of the latest long IO is: 0x000000af9b2000
Request to post your feedback. Appreciate all your time .
Best regards,
SQLNSG

Its dedicated sqlserver.

Definitely u had I/O bottleneck & CUP also.

In ur attachment most of the counters maxed-out.

Already i asked what's the disk configuration .

Ask ur network team it's local storage or SAN.

SQL Server Best Practices Article

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

SQL Server has encountered 32 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [G:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000700. The offset of the latest long IO is: 0x000000af9b2000

what is the configuration for ur tempdb ?

I guess u have all the database files, log files & tempdb same drive.If its correct separate it.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 3

I could feel that you have to work on configuration your file system.

1. Use as much number of tempdb files as of your cpu.

2. separate your tempdb file location then that of other user db.

3. Its good to have autogrouw ON but also recommend to grow the file size periodically as "time out" could also due to autogrow by system. we can autogrow during non peak hrs.

4. Distribute the IO across different disks (looks like high load on only one disk G:.

5. What is disk configuration?

Performance tuning is not a one time job, we have to keep monitoring the system periodically and things which was good yesterday might not necessary tobe good today. Keep monitoring. - could be light weight trace or work on development to check the query performance during problematic time. Also. error you provided could be due to IO or filter drives as well. http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx

Strongly recommend Ken England's Book on performance tuning to read once, great book for sql server 2000.

HTH Vinay

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

sqlnsg Old Hand Points: 364 More actions · Answer 4

HI,

The following is the Disk Configaration , Its a local SAN IBM DS 4800 (4 TB) connected through Fiber Channel dual port HBA cards . Its not dedicated san for sql this is used by other apps as well.

I have partitioned the files MDF and NDF files acrose the Raid 5 strip

The following is the placement of my files and each file is placed in seperate drive.

Raid1 Mirroring --1 ndf file

Raid5-- MDF, NDF

Raid 1,0 --NDF file

Raid 1 --quorum, tempdb , ldf --Each has seperate disk

I have made sure temp db has enough space and all the file groups are set to auto grow, and everynight i have scheduled sql service recycle as well .

Steps taken so far:

Increased memory for SQL to 16 gb

Set the cost of query execution plan from 5 to 8

As its 8 cpu , i have set the max degree of parallesim to 8

My last option left would be increaseing max worker threads from 255 to 288 .

Hope this helps.

Best Regards,

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 5

Hi sqlnsg,

I tweet Gail Shaw to look on to your issue, she is expert on performance tunning.

also tweet Jonathan...

Lets see.

Thanx.

Vinay

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

sqlnsg (1/8/2010)
HI,
The following is the Disk Configaration , Its a local SAN IBM DS 4800 (4 TB) connected through Fiber Channel dual port HBA cards . Its not dedicated san for sql this is used by other apps as well.

The fact that it is a SAN doesn't tell us much for how its configured. I pulled the spec's for that model of SAN and it can use FC or SATA drives:

Supported drives with expansion unit:
2 Gbps SATA: 7.2K rpm, 400 GB and 250 GB
4 Gbps SATA: 7.2K rpm 500 GB
2 Gbps FC: 15K rpm, 146 GB/73 GB/36 GB (E-DDM)
2 Gbps FC: 10K rpm, 300 GB/146 GB/73 GB (E-DDM)
4 Gbps FC: 15K rpm, 300 GB/146 GB/73 GB/36 GB (E-DDM)

I have highlighted what would be the ideal drives for SQL Server's use. Since you said that this is a shared SAN solution, I would take a guess that you have a configuration that uses larger disks. The worst configuration would be the SATA 7.2K RPM disks which would be a performance bottleneck under SQL load alone, but be even worse under a shared configuration.

You need to talk to your SAN Admin to identify exactly how the storage for the SQL Server is configured. You need to know for each LUN the # of drives, RAID Level, and it should be dedicated storage to SQL Server only. The model SAN you list has a maximum cache size of 16GB, so disk performance is critical.

I have partitioned the files MDF and NDF files acrose the Raid 5 strip
The following is the placement of my files and each file is placed in seperate drive.
Raid1 Mirroring --1 ndf file
Raid5-- MDF, NDF
Raid 1,0 --NDF file
Raid 1 --quorum, tempdb , ldf --Each has seperate disk

There is not enough information here to offer advice. You need to list each file, and the number of drives for the LUN it sits on. RAID 5 pays a penalty for writing data due to the cost to calculate the parity bits. RAID 1 for a data file is going to be a performance bottleneck, its the same as having a single disk, its just mirrored. You don't get the needed MB/sec or IOPS from a 2 disk RAID 1, the RAID 1+0 is the ideal configuration, and your TLOG's and tempdb should have that configuration, not a RAID 1.

I have made sure temp db has enough space and all the file groups are set to auto grow, and everynight i have scheduled sql service recycle as well.

What are the autogrow and default file sizes for tempdb set to? SQL 2000 can't instant file initialize so you want your files to be sized at service start up so that they don't have to grow under normal operation. If the files grow to 20GB in general load then set the startup size to be larger than that, say 24-28GB. Then set AutoGrowth to a fixed size like 256MB or 512MB so that file growth completes fast enough to prevent a hung process.

Steps taken so far:
Increased memory for SQL to 16 gb
Set the cost of query execution plan from 5 to 8
As its 8 cpu , i have set the max degree of parallesim to 8
My last option left would be increaseing max worker threads from 255 to 288 .
Hope this helps.
Best Regards,

Increasing memory is good for SQL under almost any scenario. I wouldn't have MaxDop set to 8 for 8 logical CPU's, I would use 2 or 4, especially if it is a OLTP workload and not a data warehouse workload. How many processor sockets does the motherboard have, and how many cores are on each processor? What type of processors are they and do they have Hyperthreading enabled? If you have Hyperthreading enabled, and the processors are older than the Xeon 5500 or Nahelem series processors, I'd start with disabling hyperthreading as it can be problemattic for SQL except on the latest processors. Even then I don't know that SQL 2000 hs ever been recommended with HT enabled.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

sqlnsg (1/8/2010)
My last option left would be increaseing max worker threads from 255 to 288 .

I think I ran out of space because it threw an error saving my last post.

I had to break out the Guru's Guide to SQL Server Architecture and Internals for this one. Whether or not you should attempt changing this depends on a number of factors, the primary ones being the CPU and Hyperthreading related questions I asked in the last post. Also what is the average number of user connections (SQLServer: General Statistics\User Connections)?

The general recommendation is to not change the setting for max worker threads, but that predates the dynamic value in 2005 and 2008 and 8 and 16 way servers being cheap hardware. SQL Server splits the number of workers evenly across the schedulers. For 256 workers, you get 32 workers per scheduler with 8 logical CPU's. If you have more than 256 concurrent connections, the workers are pooled per scheduler. When a connection is made, it is assigned to the scheduler with the fewest connections. Once a connection is bound to a scheduler it stays there. If all workers on are in use new tasks will have to wait for existing tasks to complete.

If you have 8 processors and 256 threads, 32 workers to a scheduler, and 400 concurrent connections, you could end up scheduler imbalanced for your connected users over time. If you have a worker that locks resources and causes excessive blocking (32 or more blocked processes in this case) and all of the processes were on the same scheduler, the scheduler would be unable to process new requests until the blocking was cleared.

Increasing workers might not solve that problem but then again it might. Adding 32 workers to get to 88 isn't going to be much difference, because you only get 4 additional workers per scheduler. You would have to make a significant change, but that can impact performance if you aren't careful. I wouldn't go higher than 512 workers with 8 physical processors/cores. Note that I said physical processors here, not logical. Hyperthreading presents double the physical processors to Windows OS as logical processors, so a 2 socket Dual Core Server would only have 4 physical cores, but with hyperthreading enabled, it would present 8 logical CPU's to windows equating to 8 schedulers in SQL server.

Now take your problem with stalled IO for tempdb, 32 instances taking longer than 15 seconds might make a bit more sense for why you have a issue with a stuck scheduler. If the IO system is the root of the problem, put all the workers you want in there, the problem will continue because the issue isn't with worker overload, its with IO. My bets are on IO being your problem personally.