April 13, 2011 at 2:29 pm
Hi fellow DBAs,
I've have a SQL Server 2008 instance that has been giving me some performance issues.
When I repeatedly run the same query (select * from a table), I get fairly consistent run times, then every now and again I get a much longer query run time.
I'd really appreciate some help and advice in tracking down and resolving this issue.
What information do I need to provide.
April 13, 2011 at 2:38 pm
Does anyone else have access to this database or server? If so, what are they doing on it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2011 at 2:38 pm
I don't know so much as information to provide as much as things to consider. What else is running on the server? Who is connected to the server? what are they doing? What is you memory configuration? How many DB's and what is being accessed? What is locking like? Is there any blocking? There is a whol buffet of information in SQL that can affect performance it is really more like being a CSI and seeing how the parts fit together.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 13, 2011 at 2:39 pm
Hi,
tell me about the server? what server is it? Are some other services running on this server? what storage the server use? Is it virtualized? Are there running other databases on this server which are heavily used?
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 14, 2011 at 7:21 am
It sure sounds like contention. Next time it's running slow, start with sys.dm_exec_requests and see what the process is waiting on and if it's blocked. You can proceed from there depending on what you find.
"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
April 30, 2011 at 9:35 am
Hello,
We just experienced a similar issue--severe hits every couple of minutes and found the culprit.
It was an HP server, and the culprit was HP's SNMP monitoring agent. Every couple of minutes it brought the database to a crawl. This was a relatively small db and we were in rollout with just a few users and it was being choked to death. A dozen people were looking at every SQL Server analyzing tool under the sun for poorly designed queries, etc. The network admin thought to turn off the SNMP agent to eliminate every nonessential server process, and instantly the problem went away.
Just thought I'd add that for anyone with a similar issue who stumbles upon this thread.
--Jim
April 30, 2011 at 10:08 am
This could be due to the Resource contention or Blocking happening on the server.
Select * from kind of queries need to be avoided as much as possible. Select only the Required columns.. in the select queries.. This will avoid a lot of problems..
Try to run a brief Perfmon trace as well as the profiler during the slower run times and then figure it out which queries are affecting the performance so on by comparing the Perfmon and Profiler traces.
Please provide more info abt the Server.
1. OS Version and 32 or 64 bit
2. Memory Settings
3. SQL Server Level configurations
Also try to enable Optimize for Adhoc queries option available in SQL 2008 which will boost the performance .
Thank You,
Best Regards,
SQLBuddy
May 1, 2011 at 1:26 am
Have you applied Service Pack 2 if this SQL Server 2008 or CU6 if this SQL Server 2008 R2,what is the server configuration
Total Physical Processors
Total Logical Processors
Total RAM
Number of Physical Drives
First applied Service Packs and Cummulative updates
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 2, 2011 at 10:20 am
1) run the free sp_whoisactive to check for blocking, resource contention and lots of other stuff
2) there are LOTs of things that could be at fault here. Way too many for a back-and-forth bunch of postings on a forum. Hire a professional tuner and you will probably have your answer in a matter of minutes
3) do a file IO stall and wait stats analysis
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2011 at 2:59 pm
Without knowing more about your server, database, how and by whom it is used, this post is a long shot but I'm going to post it just in case it is helpful.
Occasionally, I have had to execute a query that is a full table scan. If the source is a big table, and not already in RAM, then the entire table has to be read from disk into RAM which is comparatively slow.
If I immediately re-execute the same query, it takes only a fraction of the original query's time to retrieve the data because the table is already in RAM.
If I wait an hour and re-execute the same query, the source is usually no longer in memory (it has been overwritten with data for other queries) and has to, again, be retrieved from disk, which is comparatively slow.
I've watched these activities on Windows Performance Monitor to verify them. It is obvious when the query is fetching data just from RAM; there is no rise in disk activity associated with it, only a rise in CPU activity. It is also obvious when the query is fetching data from the disks.
Could your performance problem be this simple?
LC
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply