Sunday, April 21, 2013

[SQL Server 2008 issues] Very interested to know what can be done in the case of a long running select query

[SQL Server 2008 issues] Very interested to know what can be done in the case of a long running select query

Very interested to know what can be done in the case of a long running select query

Posted: 20 Apr 2013 09:21 AM PDT

Hi everyone. I'd love to get some help understanding why SQL Server 2008R2 x64 Enterprise Edition does what it does.The SQL Server is dedicated to an application that as I understand it processes workloads which take 5-6 days to complete and then begins processing the next workload.Throughout the week, there are constant inserts/updates/deletes that take place in different SPIDs. During the weekly workload there is a single select query that stays active for almost the entire time. This query does nothing that I can interperate as database activity but holds several locks which prevent me from doing maintenance etc on the SQL Server. The SQL Server has 48 cores and 128GB RAM. We have MDOP set on this instance to 32. Tried MDOP settings of 0, 1, 6, 8, 16, and 32 but have not determined what setting would work best. If I recall, there were a lot of CXPACKET waits when MDOP was set to 0. Parallism threshhold has always stayed at the default value of 5.Subtree cost in the execution plan for this log running select query started at 9000+ but with the addition of a few indexes and even an indexed view, we have reduced the cost to ~90. This seems like a big win but the query still stays open for days with no apparent database activity. The common wait type for the query is ASYNC_NETWORK_IO which lasts for about 2 second on average so I believe that the bottleneck may be the application slowly taking in the data collected innitialy by the long running select query. Does that make sense? I wonder if there is anything we can do on SQL Server's side to improve things. My thought is that since SQL Server is parallelizing the query to 32 threads and then just sitting there for days, it is really not taking advantage of the parallel threads but instead blocking other operations from using them. Could a plan guide that forces MDOP of 1 for this specific query free up the other threads? Mainly, is there anything I can do to prevent the query from staying open for days?Please see the below output collected by Spotlight on SQL Server for the long running spid. Is is only a moment in time but it is pretty typical of what I see all week. Wish I knew how to format it better so the header rows lined up with the output.[code="plain"]SPID Status Blocked By Last Command Memory CPU ms/s I/O per sec Logical Reads Reads Writes Total CPU Total I/O Total Logical Reads Total Reads Total Writes Current Wait Time (ms) Last Wait Type Last Wait Resource Last Batch Time Time Since Last Batch Tran Count Host Process Net Library Login Time93 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:59[/code]Thanks for reading and any insight.Howard

Huge log backups after Ola's defrag and maint script runs

Posted: 20 Apr 2013 03:18 AM PDT

I have a SQL 2008 R2 instance that's hosting several OLTP databases for LOB applications. Every night Ola Hallengren's index defrag script runs, followed by a full nightly backup (using Ola's backup scripts). Log backups are taken every hour from 3 am (after the nightly backup concludes) until 11 pm (right before the nightly index defrag script runs).Most backup log files are just a few MB, or even less than a meg. However, the 3 am log backup file often runs between 7 GB and 11 GB because of the index defrag process.My question is whether it would be a good idea to toggle the database recovery model to simple then back to full immediately before the nightly backup runs. The idea would be to get one last good log backup beforehand, then intentionally break the log chain right before the full backup runs since I would never need to restore prior to the latest full backup.What are your thoughts on this idea? Or, should I just live with the large log backup files?Thanks in advance for your advice,Andre Ranieri

Help with Triggers

Posted: 20 Apr 2013 08:12 AM PDT

I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update the a column named num_rentals in a table named MOVIES each time a customer rental record has been added, deleted or inserted.Thanks.EDIT: completed triggers removedINSERT and DELETE triggers work. I now need to create an UPDATE trigger. See 3rd post.

What happens when there is update to rows (in terms of database internals)

Posted: 20 Apr 2013 07:09 AM PDT

Hi,What happens when there is update to rows (in terms of database internals)?Thanks

How do I get max value based on count?

Posted: 20 Apr 2013 07:08 AM PDT

I have a table that has some result that scan over a period of several days. I want to display a value who's count is the largest over the time range. For example I have the following values in a column.EmpID Name Value1 Tim One1 Tim Two1 Tim Three1 Tim One1 Tim One1 Tim Two1 Tim OneSince One shows up the most, I want to display that value. I would like my output to be EmpId Name Value1 Tim OneThe other scenario is when I have two counts that are the same. For example:EmpID Name Value1 Tim One1 Tim Two1 Tim Three1 Tim One1 Tim Two1 Tim Two1 Tim OneIn this case, I would want to select the maximum of value which would be Two in this case.

large object inBuffer cache alert

Posted: 20 Apr 2013 04:21 AM PDT

HiCan anbody advise on how to handle an alert that tells me i have a large object in the buffer cache. Do i need to clear them out or does sql server flush these out after a certain retention period ? How do i find out what the object is that is causing the issue ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog