| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2011-01-20 : 16:10:08
|
| I just upgraded a client site from SQL 7 to SQL 2008 (new server box, much better/faster machine), following the recommended upgrade path. Backed up database in SQL 7, restored it to SQL 2005, detached it from 2005 and attached it to SQL 2008.The client is complaining that everything is incredibly slow. Performance is horrible. There are about 25 concurrent users. And this on a new, much more powerful server. The database has been reindexed. Statistics updated. Performance Monitor on the new server is showing that it's hardly overworking, never reaching above 10%. Database is approx 9 GB.What are the possible causes here? |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-01-20 : 16:22:19
|
| Is this x64 or x86 hardware?How much memory do you have available - allocated?If x86 and more than 4GB of memory, did you set the lock pages in memory policy and enable AWE?Is the IO subsystem able to handle the workload?Do you have the data files, log files and tempdb on the same drive? If so, is that LUN on a SAN with enough spindles to handle to workload?Just a start on some of the issues I would review.Jeff |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-01-20 : 17:33:13
|
| It's x64. The machine has 8 GB of memory. There isn't a minimum amount of memory allocated to SQL Server, but the server isn't really running anything else besides for the OS.I just set the Lock Pages in Memory option. Hope it helps.The workload isn't high. That's what's puzzling about this whole thing. I mean, this db came off a box with 1 GB of RAM, a tiny hard drive, and a single old processor, yet performed better than on this new box with a huge hard drive, dual processors, and 8 GB memory. There are no locks or blocking. The longest almost any statement/batch took in Profiler was maybe 40 milliseconds, most taking less than 10 or even 0,1, and 2 being the most common. I could of course check execution plans and such, but it seems irrelevant given that the db was performing well on SQL 7 with outdated hardware.I'm inclined to think it might be something in the difference between the way SQL 7 and SQL 2008 work with networking/listening on IPs for SQL traffic, or something like that. Or some security mechanism running either under SQL or under Windows 2008, causing the bottlenecking. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-01-20 : 18:34:26
|
| Of course. I will.The maximum value is 2147483647 MB.I didn't check Profiler against the old server. It's been taken down. But 40 ms is a very insignificant number if it only appears a limited number of times with a trace on all users and a total of a thousands of trace lines/statements covering about 10 minutes of working time. Most of the statements took far less, yet the people are complaining their workstations are slow. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-20 : 22:43:07
|
quote:
Originally posted by tkizer
You'll have to restart SQL in order for lock pages in memory to be set.
Not to refute Tara, but for the sake of completeness, this isn't correct. Lock pages in memory takes affect immediately, with no restarts of any service and no reboot.I know this because I just found a server about 10 or 15 days ago that I (or somebody) must've failed to grant it for.  Never want to see SQL Server paged out memory in my event logs again... |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-01-21 : 11:49:19
|
| Activity Monitor is strongly pointing to a Network I/O issue. Ridiculous wait times on those.I programmed the client code. It doesn't simply request large sets of data without using them, and didn't have these issues on SQL 7. So what are the network utilization differences that I can fine-tune here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-24 : 06:35:01
|
quote:
Originally posted by simondeutsch
Activity Monitor is strongly pointing to a Network I/O issue. Ridiculous wait times on those.I programmed the client code. It doesn't simply request large sets of data without using them, and didn't have these issues on SQL 7. So what are the network utilization differences that I can fine-tune here?
How much is the Batch Requests/sec ?I believe a 100MB NIC can easily handle about 3000 requests per sec.PBUH |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-01-24 : 12:11:29
|
| Pretty much never more than 50 or 60 requests per sec. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-04-05 : 11:18:34
|
| Still having this ongoing issue. What I am seeing in Profiler is that there appears to be an Audit Login event before the query runs, and an Audit Logout event afterwards. The query itself takes a mere couple of ms to run, but the front end just waits for like a half minute for the query (a simple read of several columns and several hundred rows on a table, indexed) to return a cursor. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2011-04-05 : 11:23:11
|
| Oh, and the client is requesting a client-side, static, read only cursor. The activeconnection of the recordset gets disconnected as soon as it returns so as not to keep hogging the pipe. |
|
|
|
|
SQL Server 2008 SLOOOOOW
I'm Windows 2008 EE with SQL Server 2005/2008 EE.Tara Kizer