October 17, 2006 at 5:22 pm
Hello, I was hoping you could help me solve an on going issue we have. Our company hosts and as an ASP a SQL Sequel Server Database. It is an online, real time, transactional database. It lives in a dedicated Windows domain and is only used for our product.
It lives on a Windows 2003 Server. It runs Active/Passive SQL Server clustering, and it is log shipped.
The issue we have is that performance for the online transactions is either spot on (with response times under 1 second) or it is dog poo with the response times 2.5 seconds or more. These times all relate to DB data retrieving.
During some troubleshooting, we've noticed that if there is an attached copy of the online database (which is definately not used, as shown by the current activity) we halve the performance. We need this copy for End of day activities, but can drop it during the day.
Worse still, we did detatch this copy of the online database during last nights EOD run, and the performance was still sucky, back to the 2.5 second response time. But when we later (4 hours or so) attached and then detatached it again, and the performance was back to it's sub 1 second.
It seems that the presence of this unused DB is some how hogging SQL Server resources, but have not noticed the obvious Windows performance metrics (cache hit ratio, memory etc.) going down the pan.
So does anyone know of what else I could try to work out a.) where within SQL Server it is losing resources, and b.) if you've heard of this before?
Thanks 1million for your time.
Dan
October 17, 2006 at 5:32 pm
run profiler(filter using db_id ) to find the activity going on to that database
October 18, 2006 at 12:32 am
Hi KP Kumar.
I've done that and there is nothing running against it. I know that sounds strange, but to confirm, I'm 100% there are no SPIDs running agains this DB. I feel it must be some internal allocation of SQL Server Memory, or something else, but I just don't know what.
I'm so confused!
Dan
October 18, 2006 at 1:25 pm
Did you monitor the memory counters that time? Is there any memory pressure?
monitor SQLServerBufferManager counters Buffercachehitratio, Pagelifeexpectancy
etc and find any major differences.
October 18, 2006 at 5:15 pm
Thanks. Will give it a go.
Are we the only organisation that has seen this I wonder...
Hum... Ta.
Dan
October 19, 2006 at 8:55 am
I really doubt that attaching / detaching a database would affect performance. You said "we did detatch this copy of the online database during last nights EOD run, and the performance was still sucky, back to the 2.5 second response time." which says to me that something else is going on. Since you are running on a Cluster have you checked NT logs? Does your Cluster have sufficient RAM? You are also Log Shipping... are the log backups taking a long time or are the files large? Do you have scheduled jobs that might be running during those times? How about poorly written reports?
We also have an active / passive Cluster which is also Log Shipped so I'm familiar with the setup, and I would suggest that you look elsewhere for your performance problem.
October 19, 2006 at 12:13 pm
Here are anumber of general questions that probably need to be answered:
At present there are too many potential course of action to take. Quite possibly without any added performance benefit.
However there are a few thing that you can take action on almost immediately. Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 22, 2006 at 6:34 pm
Hi guys,
thanks for all the feedback. Just to confirm that i'll be giving it a perf mon check later today...
wish me luck.
D
Dan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply