December 1, 2014 at 2:18 pm
Hi
I work on an instance where there 30 databases and all get synchronized from one main database - provider db.
An import web service imports data in the provider db every second.
Databases are all small, about 4GB size, recovery model is simple for all. The application uses snapshot isolation level, and tempdb is used massively. There are 13k trns(inserts,updates,merges)/sec on the instance.
Tempdb is configured according to the recommendations for a busy tempdb. However, only one disk (raid 10) is used for all databases and the OS. RAM is 128GB, and the cpu is 12 cores. Avg CPU usage is about 40%. We use standard edition. Writes are 1000/sec and reads are 100/sec. PLE is very high. There is a high number of latch waits, and XTS_MGR dominates regarding the collisions.
Indexing strategy is very good, not to many not to less, and queries are all covered. I put attention on the implicit conversion for queries. Queries are mainly parametrized.
All of the best practices for configuration of sql server instances and databases are applied.
I wonder if we install two additional ssd disks (128GB each), how would the spinlocks decrease so I don't have too much of them and loose deadlocks? Deadlocks are not very high, but happen, let's say some tens per hour.
I plan to use the ssd drives for the tempdb, busy indexes and log files. Should I expect SSDs will help significantly?
I appreciate your experiences, suggestions and advises...
Thanks,
Igor
Igor Micev,My blog: www.igormicev.com
December 1, 2014 at 3:57 pm
Or, ... Does someone think it won't bring significant improvements considering the big enough RAM? Every attitude is welcome...
Thanks
Igor Micev,My blog: www.igormicev.com
December 2, 2014 at 2:07 am
Hi,
I would have thought that if you are using tempdb heavily, placing it on a faster IO subsystem would improve performance. But how much? I can't really say, do you have a development system that you can test before making any changes to production?
The SQLSkills guys have blogged about spinlocks here:- http://www.sqlskills.com/blogs/paul/category/spinlocks/
I'd recommend you having a look through, they know their stuff.
December 2, 2014 at 3:07 am
DBA From The Cold (12/2/2014)
Hi,I would have thought that if you are using tempdb heavily, placing it on a faster IO subsystem would improve performance. But how much? I can't really say, do you have a development system that you can test before making any changes to production?
The SQLSkills guys have blogged about spinlocks here:- http://www.sqlskills.com/blogs/paul/category/spinlocks/
I'd recommend you having a look through, they know their stuff.
Thanks for the replay. I'm already working on the issue, but me too, cannot estimate if it will be a significant improvement. My confusion is that the RAM is big enough to get all databases read in it...
Igor Micev,My blog: www.igormicev.com
December 2, 2014 at 3:16 am
Why are you looking at SSDs? What suggests that you have a IO contention?
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
December 2, 2014 at 3:32 am
GilaMonster (12/2/2014)
Why are you looking at SSDs? What suggests that you have a IO contention?
Many stored procedures are using temp objects, and their execution count is too high. Some have high logical reads. All databases use snapshot isolation level, ...
Igor Micev,My blog: www.igormicev.com
December 2, 2014 at 3:37 am
So you have procedures running very often, doing a lot of reads from memory...
Again, what is making you think there's an IO-related problem here? You're talking about getting SSDs, but without any mention of what IO-related problem they're expected to solve.
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
December 2, 2014 at 3:51 am
GilaMonster (12/2/2014)
So you have procedures running very often, doing a lot of reads from memory...Again, what is making you think there's an IO-related problem here? You're talking about getting SSDs, but without any mention of what IO-related problem they're expected to solve.
Are the SSDs only for IO related problems?
Igor Micev,My blog: www.igormicev.com
December 2, 2014 at 3:55 am
Well, they're fast drives, what other kind of problem are you expecting them to help with?
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
December 2, 2014 at 4:00 am
GilaMonster (12/2/2014)
Well, they're fast drives, so what other kind of problem would you expect them to help with?
You're right. I should devote more effort on the objects' executions. However we have them as an option.
Igor Micev,My blog: www.igormicev.com
December 2, 2014 at 4:46 am
Identify the problems that you're having, pinpoint the causes and address those directly. Just throwing hardware at the problem may well have no effect or may make whatever problems you're having worse.
If you're not sure where to start, this may help: http://www.red-gate.com/community/books/accidental-dba
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
December 2, 2014 at 8:16 am
GilaMonster (12/2/2014)
Identify the problems that you're having, pinpoint the causes and address those directly. Just throwing hardware at the problem may well have no effect or may make whatever problems you're having worse.If you're not sure where to start, this may help: http://www.red-gate.com/community/books/accidental-dba
Yep, I know the book, Thanks.
Igor Micev,My blog: www.igormicev.com
December 3, 2014 at 12:18 pm
GilaMonster (12/2/2014)
So you have procedures running very often, doing a lot of reads from memory...Again, what is making you think there's an IO-related problem here? You're talking about getting SSDs, but without any mention of what IO-related problem they're expected to solve.
I got some improvements by doing some code refactoring so that deadlocks are less.
These are the wait types:
PAGELATCH_EX:tempdb:6(*)
IO_COMPLETION
LATCH_EX [ACCESS_METHODS_DATASET_PARENT]
ASYNC_NETWORK_IO
Igor Micev,My blog: www.igormicev.com
December 4, 2014 at 1:55 am
Page latch, don't know, would need to see the exact resource.
The latch wait is related to parallel table scans. Tune your queries to avoid large table scans running in parallel
ASYNC_NETWORK_IO is a wait for the client app to accept the data
IO Completion I think is autogrows, but not sure. It's not normal page reads.
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
December 4, 2014 at 3:44 am
GilaMonster (12/4/2014)
Page latch, don't know, would need to see the exact resource.The latch wait is related to parallel table scans. Tune your queries to avoid large table scans running in parallel
ASYNC_NETWORK_IO is a wait for the client app to accept the data
IO Completion I think is autogrows, but not sure. It's not normal page reads.
Thanks Gail,
Took care of Page latch.
ASYNC_NETWORK_IO - we already know the client app has some issues with a web service.
Could be IO Completion due to indexes reorganize and stats updates? I often see IO Completion during rebuild, but we're on a standard edition and we reorganize indexes. We're doing it at every 30 minutes, because an import service imports data constantly, and additionally we purge data on daily basis, and on hourly basis.
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply