mike1142
asked on
SQL Server Slowness
I have a customer and their SQL server VM is experiencing slow performance. There is a dizzying array of information out there and it is difficult to know where to start looking.
The longer story. SQL Server was OK at first. No major issues performance-wise. The database sits behind a low use OLTP of a different database type (hierarchical) so it is fine even at this slow of performance. Unfortunately I (they) need to run some pretty intensive queries. These queries actually ran fine at first. I bench-marked them at 1-3 minutes.
Then the SA starting asking about disk space and whether we can free up some. He wanted to move a backup file that had been sitting around since 2011. That got shot down for some reason and it was left alone.
I set up a webinar with the customer to show how the queries worked etc. And none of them seemed to want to run like they use to. Hmm so I spent the weekend looking at executions plans etc and got an out of disk space warning from the server. Well let the SA know and I will check back. Monday came and SA called to say tempdb was 50GB in size. What? He wanted to shrink the database I said let see if we can find the problem. Nothing I did uncovered anything so I ended up shrinking the file (database did not work) I set the initial size to 1 GB. It stayed there for a couple of days and then I started running the queries again tempdb started climbing. The performance of the queries was not better either. We tried different (virtual ) hardware configurations and did not find anything to work better and some made it worse.
So I have gotten several sources Brent Ozar, Adam someone with sp_whoisactive. Information from the redgate sites. I don't know where to go. They are on a very old, unsupported (RTM) version of SQL server 2008. Is that a place to start or looking at these diagnostic query ouput?
If you have anything for me please be specific on what should be done and what is good and not good. Lots of sites with run perfmon and run these scripts. They do not mean anything useful to me since I do not know what to expect. I am pretty much at a step-by-step mode now.
Thanks
The longer story. SQL Server was OK at first. No major issues performance-wise. The database sits behind a low use OLTP of a different database type (hierarchical) so it is fine even at this slow of performance. Unfortunately I (they) need to run some pretty intensive queries. These queries actually ran fine at first. I bench-marked them at 1-3 minutes.
Then the SA starting asking about disk space and whether we can free up some. He wanted to move a backup file that had been sitting around since 2011. That got shot down for some reason and it was left alone.
I set up a webinar with the customer to show how the queries worked etc. And none of them seemed to want to run like they use to. Hmm so I spent the weekend looking at executions plans etc and got an out of disk space warning from the server. Well let the SA know and I will check back. Monday came and SA called to say tempdb was 50GB in size. What? He wanted to shrink the database I said let see if we can find the problem. Nothing I did uncovered anything so I ended up shrinking the file (database did not work) I set the initial size to 1 GB. It stayed there for a couple of days and then I started running the queries again tempdb started climbing. The performance of the queries was not better either. We tried different (virtual ) hardware configurations and did not find anything to work better and some made it worse.
So I have gotten several sources Brent Ozar, Adam someone with sp_whoisactive. Information from the redgate sites. I don't know where to go. They are on a very old, unsupported (RTM) version of SQL server 2008. Is that a place to start or looking at these diagnostic query ouput?
If you have anything for me please be specific on what should be done and what is good and not good. Lots of sites with run perfmon and run these scripts. They do not mean anything useful to me since I do not know what to expect. I am pretty much at a step-by-step mode now.
Thanks
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I forgot to mention that these queries are 'certified' and modification will uncertify them They also run fine with other customers.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was afraid this would go this direction. The VM has 5 gb. The sa wanted to limit ss to 4gb. So other than ss is using all if it's memory as usual I am not sure how to answer your questions.
ASKER
Ozar's query said the page life expectancy is 38 seconds. I'll run the other query in the am. Well later today's anyway.
All the queries run with RECOMPILE
All the queries run with RECOMPILE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Guy Hengel [angelIII / a3] - Sorry by the light of day getting these values was obvious. Much too tired last night:
Box = 6GB 5.75 Usable
sqlserver.exe = 3.7gb
Total memory in use: 5.41 GB
Max Memory: 3584 MB
I can't decide on my side about running ssms on the box. It's a complicated setup to the server over a tunnel and involves network guys to change or add to this. I can check into alternate access if really necessary or maybe after this improves some.
Page file is on c and d this is something that was changed at the first signs of slowness. c: min/max 10000 d: system manged. No to dedicated.
data files are on e dedicated. log files are on d shared with other stuff mentioned and there are other databases if those need to be checked.
Box = 6GB 5.75 Usable
sqlserver.exe = 3.7gb
Total memory in use: 5.41 GB
Max Memory: 3584 MB
I can't decide on my side about running ssms on the box. It's a complicated setup to the server over a tunnel and involves network guys to change or add to this. I can check into alternate access if really necessary or maybe after this improves some.
Page file is on c and d this is something that was changed at the first signs of slowness. c: min/max 10000 d: system manged. No to dedicated.
data files are on e dedicated. log files are on d shared with other stuff mentioned and there are other databases if those need to be checked.
ASKER
ste5an you query produced a value of 2576
Please keep in mind. I am not running the queries at the moment until the issues with the box are addressed.
I am concerned about the version of ss 2008 they are running it is RTM which is unsupported. Can this be part of the issue? If so how would you go about patching? slap sp3 on and go or perhaps something more gradual?
Please keep in mind. I am not running the queries at the moment until the issues with the box are addressed.
I am concerned about the version of ss 2008 they are running it is RTM which is unsupported. Can this be part of the issue? If so how would you go about patching? slap sp3 on and go or perhaps something more gradual?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ScottPletcher - That view does not have a column called that. I have
physical memory in use: 3918720 kb,
large_page... 0
locked_page... 0,
total_virtual_address_spac
committed 3989484
virtual_address_space_avai
8583546952
page_fault_count 2377548
memory_utilization_percent
physical memory in use: 3918720 kb,
large_page... 0
locked_page... 0,
total_virtual_address_spac
committed 3989484
virtual_address_space_avai
8583546952
page_fault_count 2377548
memory_utilization_percent
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes I have been trying to use that tool but there does not seem to be a good source of what to measure or how to use it.
I see where I took your last paragraph to be part of the second. Sorry I did not read carefully,
I see where I took your last paragraph to be part of the second. Sorry I did not read carefully,
ASKER
Is there a way to manually flush a particular execution plan out of the cache and force a new one? Like WITH RECOMPILE with an SP.
ASKER
Hi Guys it turns out that the query was the main issue, I am not sure I was clear on everything. There was an issue with one of the columns of the primary key in the clustered index.
ASKER