[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Server Slowness

Posted on 2014-07-17
Medium Priority
Last Modified: 2014-07-19
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.

Question by:mike1142
  • 10
  • 3
  • 2
  • +1
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1004 total points
ID: 40201374
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

Author Comment

ID: 40201376
Thanks. They are really not my queries. I cannot modify then much and they do drop the temp tables appropriately.

Author Comment

ID: 40201380
I forgot to mention that these queries are 'certified' and modification will uncertify them They also run fine with other customers.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1004 total points
ID: 40201389
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?
LVL 36

Assisted Solution

ste5an earned 332 total points
ID: 40201398
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).

Author Comment

ID: 40201405
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.

Author Comment

ID: 40201408
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
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1004 total points
ID: 40201413
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?

Author Comment

ID: 40202257
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.

Author Comment

ID: 40202427
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?
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 total points
ID: 40203130
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).

Author Comment

ID: 40203393
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
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 total points
ID: 40203811
Sorry, I was unclear.

PerfMon = Performance Monitor = a separate tool from SQL Server.  You use it to test Windows for performance.

Author Comment

ID: 40203819
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,

Author Comment

ID: 40203826
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.

Author Comment

ID: 40204583
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.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question