Home > sql server 2008 r2 > T-SQL Query runs quickly on local PC, but slow on SQL Server?

T-SQL Query runs quickly on local PC, but slow on SQL Server?

December 9Hits:1
Advertisement

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters.

It's been running without issue for months - but has suddenly started running very slowly on the server (4 core, 10GB RAM, disks on fast SAN)

If I run the query locally on my PC (2 core, 2GB RAM, one local disc) then it takes approx 1:20 to run.

Prior to this week, run on the server took approx 0:15 to run.

This week, run on server taking approx 12:30 to run!!!

This is a massive difference, and I have no idea what to start looking for to resolve the issue.

What I HAVE noticed is that this does not seem to be getting cached...OR something is hogging/not releasing cache.

e.g.
Run this locally, 1:20...run it again immediately - almost instant.
Run on server 12:30...run it again immediately - 12:30 again?

Any suggestions very gratefully received!

Answers

A few of the most common scenarios for occasionally slow, but usually fast, SELECT queries:

  1. A suboptimal plan is being used - perhaps due to bad stats, parameter sniffing, or other factors. In your case, seems unlikely that it would be parameter sniffing, if you really mean both no explicit parameters and no literals (e.g. hard-coding WHERE x = 1 is still parameterized in most cases). In order to make any useful guesses about this, you'll need to supply actual execution plans after the successful completion of one of the "slow" versions of the query.
  2. Your query is getting blocked - again, this is not likely to be your issue either, since you say you are the only one working on this database. But to determine if you are being blocked, when you observe the query is running slow, do this in one window:
    PRINT @@SPID;
    
    

    Once you have that output, run the slow query in that same window:

    SELECT ... query that runs slowly ...
    
    

    Now, in a new window, run this:

    SELECT command, status, wait_type, last_wait_type, blocking_session_id
      FROM sys.dm_exec_requests WHERE session_id = <@@SPID FROM ABOVE>;
    
    

    Make note of the wait type (see below), but if blocking_session_id is not 0, check what that user is running (run the query above again, changing the session_id to the blocker).

  3. Some other resource is bottlenecked - it could be that at certain times the underlying disk that the database is on or the network you're moving data across can be under extreme pressure, and this will filter down to just about every operation that uses them. This could also be true if your SQL Server is on a virtual machine and the underlying host is under some kind of resource pressure.

    In addition to the execution plan you get from the slow version of the query, you should examine the waits that are experienced while your query is running. This is beyond the scope of this localized answer, mostly because it is a very lengthy topic to digest, but you can start with this white paper from Microsoft.

With the disclaimer that I work for SQL Sentry, our free execution plan analysis tool, Plan Explorer, tries to make problems with a given execution plan a lot more obvious than it will ever be in Management Studio. As a bonus, the paid version, which you can trial for free for two weeks, also gives details about the exact waits that your session experienced - this is pretty cumbersome to do on your own. Sorry about the ad but it really can be a time-saver that helps isolate the real problem behind a particularly nasty query.

Related Articles

  • T-SQL Query runs quickly on local PC, but slow on SQL Server?December 9

    I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server (4 core, 10GB RAM, disks on fast SAN) If I run the q

  • Why straight SQL query run faster than stored procedure?August 18

    I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec. For example : DECLARE @ip_RequestI

  • SQL query running slow compared to its earlier execution time

    SQL query running slow compared to its earlier execution timeMarch 9

    One of our application using an SQL query is reported running slow. Current execution time:- 15 minutes and so Earlier execution time:- 30 seconds. SQL-Sentry Plan for the slow query I've checked for Fragmentation and stats if they are up to date. Th

  • Linq to SQL: Query runs fine in SQL Server Management Studio, but times out on the applicationJanuary 25

    I'm maintaining the code of a legacy application. It is using Entity Framework 3.5 and updating it to a newer version at the moment is not possible. There is a query that lately has started to time out. This is the query in LINQ: var compoQueryResult

  • Why my sql query run faster than my stored procedure?

    Why my sql query run faster than my stored procedure?June 16

    I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the time of execution : 6 second but my stored procedur

  • SQL query to remove primary key assignment from column in SQL Server

    SQL query to remove primary key assignment from column in SQL ServerFebruary 17

    I'm looking for the query that does this. Tried the ALTER TABLE DROP CONSTRAINT... query but had no luck as it's not a constraint (i.e. the constraints directory is empty) --------------Solutions------------- You should be able to use ALTER TABLE/DRO

  • In SQL 2000 how do you determine what process slows down the serverJuly 20

    Sql Server 2000 is maxed out, and I am not sure why, but I believe it may be due to the fact that a query or queries were run that are slowing it down. Is there a way to determine which query caused the slow down, and at what time? --------------Solu

  • Why ST_SRID cause Geoserver generated SQL query to run about 500x slower?January 8

    When investigating why Geoserver was so slow to render my WMS tiles from a POSTGIS database I found that the following Geoserver generated SQL query run almost 500x faster when remplacing the ST_SRID("geom") function by its actual value 4326. SE

  • Queries run extremely fast locally, extremely slow on remote machinesJuly 14

    We have a query that take takes anywhere from 24-38 seconds to run when you run it from SQL Server Management Studio from a remote machine, but <1 second to run when it is run from the machine locally using SQL Server Management Studio. SQL Server 20

  • Performance tuning of a SQL queryJanuary 19

    Problem statement: I have a table which stores the customer, related customer and count of related customers. The related customer can have again further related customers it's like a recursive relationships. For example customer 1 is parent of custo

  • SQL query without temporary tableJanuary 9

    I want to modify this sql query from this post: List databases and tablespace in SQL Server 2014. IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL DROP TABLE #space CREATE TABLE #space ( database_id INT PRIMARY KEY , data_used_size DECIMAL(18,2) , log_u

  • assign alternate sql query in phpgrid column/fieldJanuary 31

    Whilst a phpgrid itself generated by a sql query it would be preferable to assign an alternative sql query in a phpgrid field. How may that be accomplished?

  • Query Running Differently on SQL 2005 vs SQL 2008R2

    Query Running Differently on SQL 2005 vs SQL 2008R2November 2

    At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes over 4hrs. SQL2005(+latest patches) is running in pro

  • When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

    When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?March 4

    Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 different tables. It's for a large-ish compan

  • PC making ugly fan / disk noises during a long running sql query?October 26

    Yesterday I encountered a strange phenomenon... in the middle of a long running SQL query (one and a half minutes in, say) there was a sudden high-pitched whirr from my PC. I stopped the SQL query, and the noise stopped. I tried it again a couple of

  • Run SQL query from cronJuly 26

    I'd like to run a SQL query via cron. I figured I'd create a .sql file and call it from a script that I place in the cron folder. The query is relatively large and not written by me, so I'd prefer to not have to re-create it in PHP. Can anyone provid

  • How to run a SQL Query for character in Post TitlesJanuary 4

    Pretty much self explanatory. I have a development server running the latest WP, and I imported a bunch of posts from a blogger account. After the import, every post was titled as such: >Example Title . What i want to do is a run a sql query to delet

  • Running an SSIS package owned by a domain user from SQL Server running on a local service accountApril 2

    I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the environment looks like this: Domain Server 1 SQL Server

  • How can I run a sql query from a file in Drupal?October 8

    I have a module that will be used as a location finder, and it needs to create a table on install that will hold zip codes and their location. That's easy to do. However, I also need to populate that table. Seeing as it's quite a large dataset, and I

  • Sql query suddenly takes a long time to runApril 24

    I have an application that is processing lots of items from a sql database. It runs along fine and then after about 4 million items processed it starts hanging up. Using sql server profiler, I traced the problem to a query that jumps from a duration

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 1.337 s.