[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Slow SQL Server Query

I have a problem that I can't seem to solve.  I have 2 Dell T7500 Computers, Each running Windows 7 and SQL Server 2008 R2.  Both have mirrored drives, 48GB RAM.  Space is not a problem.  Server1 has 8 cores and is 2 years old, Server2 has 12 cores and is about a year old.  Ultimately, I don't think that the speed of the computers is the issue.

We have an Excel/VBA application running on both servers.  The Excel apps run queries agains a SQL database.  The newer, faster Server2 hangs at a particular point.  It eventually finishes the routine it is running but Excel reports "Not responding" for a minute or so.  I initially assumed it to be a VBA problem.  However, I isolated the problem queries and moved them to Query Analyzer.  Here are what I assume to be the relevant facts...

I have a database on Server1.  Everything runs as normal.  I backup Database1 from Server 1 and copy the .bak file to Server2 and restore.  Most things seem to run fine on Server2 with the exception of two queries against the same table, T1.

T1 has 59,000 rows and is about 400MB of data (same on both servers)
When I run this query, "update T1 set [FV_FWD]= Null"  it takes less than 2 seconds on Server1 but 29 seconds on Server2.  I scripted the table to a new Query Analyzer window and used that to create a duplicate table, T2.  I then inserted all the records from T1 into T2.  So, I had two tables with 59,000 rows.  If I run the same query against the duplicate table, "update T2 set [FV_FWD]= Null" it runs in under 2 seconds like it should.  I realized that T2 lacked an index that T1 had so I created a matching index and it still ran in less than 2 seconds.  I've decided that disk, memory or processor bottlenecks are not likely the issue since it CAN run fast against the duplicate table.

We are concerned we have some setting wrong that might have an effect of a variety of queries (they might be slower than they should be and we just don't know it.)  I'm a novice at SQL so I open the floor.  Does anyone know where I should look?  

Thanks in advance for you help.  I'm sure I've provided too much info and not enough at the same time!
0
mikerich61
Asked:
mikerich61
  • 7
  • 5
  • 2
  • +2
4 Solutions
 
essaydaveCommented:
Hi Mike

Have you tried plugging the query into SQL's Database Engine Tuning Advisor?  If you go to Start > Programs > Microsoft SQL Server 2008 R2 > Performance Tools you should have it installed already.  

Save the query to a file, and the select that file to use as the Workload and run it against the database in question.  Once it completes, it will generate a number of recommendations - and an estimated improvement percentage - that you can run against the database.  It might include some new indexes and statistics and will generate the SQL scripts for you to run against the database to create them.
0
 
alpmoonCommented:
Have you checked triggers or referential integrity constraints on that column of both tables. If they are different it might be the actual cause.
0
 
mikerich61Author Commented:
I ran the SQL Database Tuning Advisor with that query against the DB.  Twice, for the fast and slow version of the table.  It had no recommendations and siad it would give a 0% increase.  I checked and there are no triggers or constraints on either. One thing I did notice in looking at the table properties at the triggers etc.  The slow table has about 350 items listed in the Statistics folder, while the fast one has about 4.  I'm not sure if that is relevant.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sachitjainCommented:
You said, "update T1 set [FV_FWD]= Null"  it took less than 2 seconds on Server1 but 29 seconds on Server2. Could you please check if some blocking is happening against rows in this table T1? If you have sysadmin permissions over SQL instance on server 2, you could run following script in some other query window while running your script. It would also run for 30 secs as per its loop.

create table #t1 (SPId smallint, BlockingSPId smallint, tStamp datetime);
declare @rowCount int;
declare @ts datetime, @ts1 datetime;
set @ts = getdate();
set @ts1 = dateadd(second, 30, getdate())
while @ts < @ts1
begin
      insert into #t1 (SPId, BlockingSPId, tStamp)
      select SPID, Blocked, getdate() from sys.sysprocesses
      where Blocked>0;
      select @rowCount = count(*) from #t1;
      if @rowCount > 1000 break;
      waitfor delay '00:00:00.050'
      set @ts = getdate()
end
select * from #t1
drop table #t1
0
 
Scott PletcherSenior DBACommented:
Some possibilities, on the slow instance:

1) the table needs rebuilt [most likely based on the specific symptoms]
2) parallelism is causing excessive waiting (as so often happens on SQL Server)

Try explicitly specifying minimal parallelism on the slow instance:

UPDATE dbo.T1
SET
    [FV_FWD]= NULL
OPTION ( MAXDOP 2 )

To check the status of the table, look at the sys.dm_db_index_physical_stats view for that table or run the following (on both instances, to compare) and report the results:

DBCC SHOWCONTIG('T1')
0
 
mikerich61Author Commented:
Ran the blocking query and it returned no results.  

Ran the query UPDATE dbo.T1
SET
    [FV_FWD]= NULL
OPTION ( MAXDOP 2 )

Made no difference.  

Ran this: DBCC SHOWCONTIG('T1') on both tables T1 and T2 and got these results:

DBCC SHOWCONTIG scanning 'T1' table...
Table: 'T1 (1443548972); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 56957
- Extents Scanned..............................: 7122
- Extent Switches..............................: 7121
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [7120:7122]
- Extent Scan Fragmentation ...................: 0.83%
- Avg. Bytes Free per Page.....................: 3758.8
- Avg. Page Density (full).....................: 53.56%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'T2' table...
Table: 'T2' (2107935377); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 56957
- Extents Scanned..............................: 7122
- Extent Switches..............................: 7121
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [7120:7122]
- Extent Scan Fragmentation ...................: 0.29%
- Avg. Bytes Free per Page.....................: 3776.3
- Avg. Page Density (full).....................: 53.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks again for everyone's input.  I'm certainly learning a lot!
0
 
sachitjainCommented:
Few questions:

1.> Is it the only query on that server that is running slow or there are others too?
2.> When did this particular server last boot up? If more than 30 days then please reboot it.


One more suggestion, please set Blocked Process threshold to 1 on your server by executing this command
exec sp_configure 'Blocked Process threshold', 1
reconfigure

Run SQL Profiler and create SQL trace with event "Blocked Process Report" under "Errors And Warnings" Event category. Run this trace while running your query. It is just an other way of capturing blocking.
0
 
mikerich61Author Commented:
I set the Blocked Process threshold to 1 and ran the query along with the query that checks for blocks.  It appears that there was no blocking.  

As far as the server goes, there is another query against this same table that also takes about 30 seconds against this table, about 2 seconds on the copy or the table on the other server.

The server has been rebooted a number of times this week.  Furthermore, after troubleshooting the query for a few days, I formatted the drive on Server2 (the problem one) and reinstalled Windows7 and SQL server and all associated updates. I backed up and restored the database from Server1 to Server2 and the problem persists.  This all happened before I posted this question.
0
 
Scott PletcherSenior DBACommented:
If the drives are local, check the drives for physical problems -- bad controller, bad sectors, etc..

If the drives are SAN, you will also need to check for potential communication issues.
0
 
mikerich61Author Commented:
The drives check out fine.  I reformatted when I reinstalled as well.  The fact that the duplicate table runs fine SEEMS to indicate to me that it's not the drives.  I will run further diagnostics though.
0
 
sachitjainCommented:
Have you tried my suggestion that I gave yesterday regarding SQL Profiling?

It looks like strange problem. Here is one workaround.
1.> Create table T2 exactly as T1 with same set of data
2.> Drop all dependencies on table T1 (foreign keys in other tables)
3.> Drop table T1
4.> Rename table T2 to T1
5.> Recreate all dependencies back to T1 those got dropped in step 2.

And then try ur query again and see how much time it takes now?
0
 
mikerich61Author Commented:
I did set the blocked process to one and ran the profiling.  Nothing.
We did copy the table prior to me posting this question and did pretty much exactly what you mentioned.  We took the slow table, table one, and made a copy calling it table two, inserted all the rows from the slow table into the copied table.  Then new table ran fine.  Then we renamed the slow table and then renamed the new, fast table the name of the original slow table.  It still ran fast.  The renamed slow table still ran slow.    

All of our tables are very simple.  No constraints or dependencies.

I also tried setting another field in the slow table to null, wondering if it was specific to the field I had been testing.  It was slow updating another float field and also and int field.
0
 
sachitjainCommented:
Very strange problem. No idea what could be the issue with that slow table. Since other table with same set of records and same name is performing well so I think you should ignore the old slow table and live with the new one.
0
 
mikerich61Author Commented:
I have tinkered with things quite a bit and finally hit on something that may allow one of you more knowledgeable people shed some light on this.    As I mentioned earlier, I scripted the slow table, edited the script to make a new version of the table and inserted all the records from the original into the duplicate.  It ran great using the insert query.  Still couldn't make the old table run faster.  The slow table has several hundred fields so I decided to delete some to make it smaller and see if that did it.  As I started to do this, I didn't want to delete the users primary key so I looked for it and realized there wasn't one.  I added an integer field that was auto-incrementing and made it a primary key and the table became fast!  I asked the guy who uses it why no primary key and he said he had added an index on a combination of other fields that were unique.  So, the table had a unique, non-clusted index but no primary key.  

My last questions are:  
The table did not have a primary key on the original SQL server we backed it up from, why was it fast on that one?  
Why did making a copy of the slow table on the same server and inserting all the same records into it make a fast table when it didn't have a primary key either?
Did backing up the database and restoring it to the new server somehow damage the non-clustered index that existed on that table, making it slow?

Thanks for all the input and I'm sorry for the rookie mistake of not having a primary key.
0
 
sachitjainCommented:
1.> PRimary key is there to uniquely identify each record in the table. It aids to performance only when your query's filters or joins make use of the column on which Primary key is there.
2.> As I told earlier, its really a strange behavior. Ideally it should not happen.
3.> No backing up and restoring DB does not break any indexes neither rebuilds them.
0
 
mikerich61Author Commented:
Thanks everyone.  I've learned a lot from the answers.  I split the points up.  All of the suggestions were helpful.  I'm not sure the underlying cause of the differences between two similar computers running the same database but I think we are headed in the right direction.  Using the various techniques you all mentioned has helped to improve our whole process.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now