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 |
|
stonebreaker
Yak Posting Veteran
92 Posts |
 Posted - 2009-12-10 : 17:53:05
|
| I am having an issue with performance of an XML query. The query doesn't actually touch any database tables. I am creating an XML variable, loading it, then selecting from the variable into a table variable, all within the query. There are 95 rows being loaded into the table variable.My problem is that despite the fact that the test server has only one processor and 6 gigs of memory, and the production server has 8 cores and 8 gigs of memory, this query takes about 5 seconds to run on test and about 3 minutes to run on production. I've looked at all the settings using sp_configure and all the settings appear to be the same on both servers.I thought it might be a disk I/O issue, especially since the production server uses a network drive to hold the data files, but when I run a non-XML query (select * into #temp_table from db_table), it runs instantly, even though the db_table has about 16,000 rows. So it seems to have something to do with the fact that this query deals with XML.Performance monitor does not show any unusual issues - the processors are hardly taxed, there is plenty and the disk I/O time is very small.I increased the size of the tempdb on production but it made no difference in performance.About the only real difference is the size of the respective databases. Test has about 400 megs of data, production about 2 gigs. But again, no tables are being queried so I can't see how that would matter.Are there any server or database settings that affect XML processing in particular? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-12-10 : 18:13:32
|
| what versions of sql r u using in prod and dev?any service packs hot fix difference between the twothere could be other processes running on production such as some developers where we are at sometimes are doing some huge bulk updates, inserts or something they should not be doing in prod..also is the prod server healthy? nic problems etc....virus, someone running packet analysis with tools such as wireshark etc..it could be any number of issues. if you are not admin, it is very hard to find out sometimes. can u post your query?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2009-12-10 : 18:32:02
|
| Now that you ask, the test server is running Enterprise 2005 and the production server is running Standard 2005 (they can be cheap around here sometimes). The production server is pretty much brand new, and its performance is not being taxed much at all. It generally has about 20 users on it, and the CPU usage never got above 30% when I was monitoring it.I don't think there are any other processes impacting the query - it takes 2 min 48 sec to run, every time. If there were impacts from other users, I feel pretty sure the time would vary by at least a few seconds.Both servers are using the same version of Windows OS and the same version of SQL Server - 9.00.1399.06, except for the aforementioned Enterprise vs. Standard edition.I'll check with the developer who wrote the code tomorrow to see if there is any proprietary info in the query. If not, I'll post it. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-12-11 : 13:42:47
|
| wow you have enterprise for test and standard for production? wow it must be nice! :) this might lead you to many unanswered questions in your environment.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2009-12-14 : 11:32:45
|
| So no one has any suggestions on what to check next?StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
|
|
|
|
|
|
XML query running slow on production, fast on test