August 10, 2012 at 7:24 am
Fly Girl (8/7/2012)
...
6, 7 & 9. A private consulting company had just done a review of db performance. From what they left from their traces & analysis the main culprit seems to point to the application blocking itself.
...
If you have a lot of blocking and you can't make changes to the application, you should consider setting the database to READ_COMMITTED_SNAPSHOT to use Row Version Isolation. That should eliminate most blocking, especially for reads.
You can read more about it here:
http://msdn.microsoft.com/en-us/library/ms177404(v=sql.90)
Row Versioning-based Isolation Levels in the Database Engine
August 10, 2012 at 7:43 am
Another thought on restarting the SQL Service: As Gus mentioned, that would dump bad plans and make the system run a little more efficient for a while.
The other thing it's doing is releasing memory back to the OS; after the restart SQL will start by taking minimum memory and then grow its pool as needed. If the server memory isn't sufficient this could certainly slow things down once the service has grown its pool as far as it can; then it has to start paging to disk. Could be that a memory upgrade would help remove pressure. As mentioned elsewhere, a little bit of time in PerfMon could help assess this.
August 10, 2012 at 8:31 am
Thanks very much, guys!
Interesting idea on the Read_Committed_Shapshot. The documentation says that 'most' applications can work with it without changes... Dun't, dun't duh... That and the issue that it uses more tempdb space may be significant. I need to read a bit more on that one. Additional opinions appreciated.
I think the post regarding clearing out memory is on target as well.
The big news is that the company has finally committed to moving to 2008 R2--by the end of the month. This will get them from 8GB of memory to 32 GB and, glory be, they will upgrade their service to full management by the co-lo. Hence, the co-lo will set up the new server to conform to all of their management/config needs and there will be well-qualified DBA's looking after this poor mess of a server by the end of the month.
Advice on the upgrade? What do I need to look out for other than attempting to find a way to test anything that connects or shares data? The consulting service they hired earlier this year gave a report that said that there were no concerns that they could identify. But I do hate surprises!
August 10, 2012 at 10:45 am
Fly Girl (8/10/2012)
Thanks very much, guys!Interesting idea on the Read_Committed_Shapshot. The documentation says that 'most' applications can work with it without changes... Dun't, dun't duh... That and the issue that it uses more tempdb space may be significant. I need to read a bit more on that one. Additional opinions appreciated.
I think the post regarding clearing out memory is on target as well.
The big news is that the company has finally committed to moving to 2008 R2--by the end of the month. This will get them from 8GB of memory to 32 GB and, glory be, they will upgrade their service to full management by the co-lo. Hence, the co-lo will set up the new server to conform to all of their management/config needs and there will be well-qualified DBA's looking after this poor mess of a server by the end of the month.
Advice on the upgrade? What do I need to look out for other than attempting to find a way to test anything that connects or shares data? The consulting service they hired earlier this year gave a report that said that there were no concerns that they could identify. But I do hate surprises!
I have set a number of production databases (50+) to READ_COMMITTED_SNAPSHOT, and never had a problem. The demands on tempdb are usually very moderate, so that has never been a problem. My experience is that it does a good job of minimizing blocking and deadlocks.
If you are moving to SQL Server 2008 or later, you can turn on the Optimize for ad hoc workloads option to prevent plan cache bloat. It is a much better option than completely clearing the plan cache, and it takes care of things automatically.
exec sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
exec sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
Also, I wouldn't put a lot of faith in the hosting provider DBAs offering valuable help in this area. 🙂 They tend to be fairly junior level people who might do a good job of setting backups and re-indexing, but wouldn't be much help for advanced tuning.
August 10, 2012 at 11:06 am
Oooo! Nice feature (ad hoc workloads). I hadn't read up on that one for 2008 before. Thanks!
August 10, 2012 at 11:23 am
I primarily use Read_Committed_Snapshot on most transactional databases these days. Not so useful on staging/bulk-import databases, but on OLTP systems it can be great.
Unless you routinely have large numbers of updates and deletes running concurrently, or on large amounts of data, the hit on tempdb is usually pretty minimal. It just has to store "before" versions of data, and feed those to concurrent selects, for the duration of update/delete operations. In most transactional databases, those actions are done on small datasets, and don't hold onto them for very long, so it hits tempdb constantly but with a light load.
On the ad-hoc option, that also works well on databases with lots of dynamic queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 10, 2012 at 11:33 am
Excellent! Thanks so much for sharing the benefit of your experience. I will plan to do some tinkering this weekend and set the snapshot option.
August 10, 2012 at 12:10 pm
Fly Girl (8/10/2012)
Excellent! Thanks so much for sharing the benefit of your experience. I will plan to do some tinkering this weekend and set the snapshot option.
Be aware that you have to kick everyone out of the database to set read_committed_snapshot on, so you should do it at a time of no or minimum activity.
The following code with do it:
use master;
alter database [MyDatabase] set read_committed_snapshot on with rollback immediate;
August 21, 2012 at 8:53 am
I like the Read Committed Snapshot iso level and recently enabled it in a database supporting a vendor app that could not be modified and it worked wonders.
One other angle, you said the database server has 8 GB memory. Is this 64-bit or 32-bit with AWE enabled? In either case check that max memory is set:
-- memory overview
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS PhysicalMemoryMB,
(
SELECT value_in_use AS MaxServerMemoryMB
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS MaxServerMemoryMB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS IsAweEnabled
FROM sys.dm_os_sys_info;
I inherited an instance with similar symptoms to what you're describing and max memory was not explicitly set. During peak times SQL Server would encroach on the memory needed for the OS to maintain stability and reports of "the app is slow" would inevitably ensue. The group before me would simply reboot the database server which would obviously clear the buffer pool and reset things to square one...until the same scenario built itself up as SQL Server needed more memory.
edit: change query to return physical memory in MB to match config option
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2012 at 10:00 am
Thanks for your suggestion. Here's what I see if I check Max Memory:
PhysicalMemoryMBMaxServerMemoryMBIsAweEnabled
8181.80 6144 0
Now, forgive me for having no idea what this means or if it is appropriate.
The SQL Server (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) is running on a 64-bit install of Windows Server 2008 R2 Datacenter
August 21, 2012 at 10:18 am
Good, at least someone set it. It's impossible for me to say if that number is appropriate because it will depend on what else runs on the database server.
Chapter 4:
Troubleshooting SQL Server: A Guide for the Accidental DBA By Jonathan Kehayias and Ted Krueger
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2012 at 10:28 am
Thanks for the link. That looks incredibly on-target for me as I start contracting rather than working as an FTE. Seems no one understands the distinction between SQL Developer and DBA.
August 21, 2012 at 10:33 am
You're welcome. If it stays in the realm of databases you're probably OK, i.e. in the same boat most of the rest of us have been at one time or another. If they start asking you to fix a copier or setup a desk phone run :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2012 at 11:03 am
Good premise, I, however, have become quite handy at fixing copiers... 😉
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply