Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lappin
Posting Yak Master
182 Posts |
 Posted - 2011-01-20 : 04:54:50
|
| I wonder does anyone have any advice? I have a query which takes a long time to run on a virtual (VMWare) server but runs much faster on my lower spec PC. Both have SQL Server 2008 R2. I did a backup and restore from VM server to my PC so database is same. I ran the same query on both machines. On my PC with 2GB RAM I ran the query for a date range of 1 day and it took 38 seconds. On the server (6GB RAM) I cancelled the same query after 8 minutes. Before I realized there was a problem I ran the query but for a bigger date range (1 month) on VM server, it crashed out because tempdb filled up. I had previously ran the same query for a month's date range on the VM server and it ran successfully, taking about 20 minutes. The query does have quite a lot of complicated joins, a DISTINCT and inserts to a table variable - so I know it is memory intensive. But it is intended to run out of hours so I would be happy if the server had same performance as my PC or hopefully a little better.I checked and found no other processes on SQL using:SELECT * FROM sys.dm_exec_requests aOUTER APPLY sys.dm_exec_sql_text(a.sql_handle) bWHERE session_id > 50and session_id <> @@spidDoes anyone know any tools to find cause of problem? |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2011-01-24 : 14:47:13
|
| I would recommend update statistics |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2011-01-24 : 15:48:33
|
| Can you give us Server specs? and Performance counter values for Memory,CPU and Disk. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-25 : 03:46:51
|
| Swap the table variable with a temp-table. They usually perform *a lot* better than table variables in big/long operations... I know some people have stopped using table variables altogether because of very inconsistent performance. - LumbagoMy blog-> www.thefirstsql.com |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-02-07 : 04:37:59
|
| I managed to fix the problem by creating an indexed view as a covering index it now takes 1 or 2 seconds per day. I also stopped using the table variable. Thanks |
|
|
|
|
|
|
virtual server query performance