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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the tempdb starts climbing, shrinking (too small) it will just keep the queries stay slow...

so, make sure that any "tables" you create are also dropped. if your scripts contains ##temp tables (instead of #temp tables or @table variables), this could explain the issue

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike1142Author Commented:
Thanks. They are really not my queries. I cannot modify then much and they do drop the temp tables appropriately.
mike1142Author Commented:
I forgot to mention that these queries are 'certified' and modification will uncertify them They also run fine with other customers.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
4 questions that I need the information from you:
how much RAM does your box have?
what is the RAM used by the SQL Server services?
what is the total RAM used by all apps on the box?
what is the max memory configuration of your SQL Server instance?
ste5anSenior DeveloperCommented:
Have checked PLE?
SELECT  object_name ,
        counter_name ,
FROM    sys.dm_os_performance_counters
WHERE   object_name = 'SQLServer:Buffer Manager'
        AND counter_name = 'Page life expectancy';

Open in new window

When it's too low (<1k) then I would check the cached query plans for phyisical IO.
        total_logical_reads ,
        total_logical_writes ,
        total_physical_reads ,
        execution_count ,
        total_logical_reads + total_logical_writes AS total_logical ,
        CASE WHEN sql_handle IS NULL THEN ' '
             ELSE ( SUBSTRING(st.text, ( qs.statement_start_offset + 2 ) / 2,
                              ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                                     ELSE qs.statement_end_offset
                                END - qs.statement_start_offset ) / 2) )
        END AS query_text ,
        st.text AS sql_text ,
        qp.query_plan ,
        cp.cacheobjtype ,
FROM    sys.dm_exec_query_stats qs
        INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   total_logical_reads + total_logical_writes > 0
ORDER BY total_logical DESC;

Open in new window

When there are no query plans with heavy phyisical IO, then I would look at those with high logical IO. This means that a lot of memory is required. Here you need to correlate the VM performance (like memory swapping).
mike1142Author Commented:
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.
mike1142Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how much RAM does your box have?  => 5GB
what is the RAM used by the SQL Server services?    => using Task Manager on the box
what is the total RAM used by all apps on the box?   => using Task Manager on the box
what is the max memory configuration of your SQL Server instance?   => checking the max memory setting in the SS
it should actually be 3.5GB and not 4GB on a 5GB box...  this is to avoid "paging" issue.

do you run the SSMS on the box itself? if yes, stop that, and run the SSMS remotely.
SSMS is using lots of memory itself, and running it on the SQL box itself will raise the above issue of "paging" memory to the page file.

is the pagefile on that box on a dedicated disk?
are the SQL data files and SQL log file on dedicated disks?
mike1142Author Commented:
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.
mike1142Author Commented:
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?
Scott PletcherSenior DBACommented:
Task Manager is not necessarily fully accurate for SQL Server.

You should use the view below, since you're on SQL 2008:
SELECT * FROM sys.dm_os_process_memory --SQL 2008+

You can also view/cross-check using:
PerfMon  SQL Server instance counter "Total Server Memory" (I think that's the name).
mike1142Author Commented:
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_space_kb 6387512
committed 3989484
virtual_address_space_available kb
page_fault_count 2377548
memory_utilization_percentage 100
Scott PletcherSenior DBACommented:
Sorry, I was unclear.

PerfMon = Performance Monitor = a separate tool from SQL Server.  You use it to test Windows for performance.
mike1142Author Commented:
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,
mike1142Author Commented:
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.
mike1142Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.