I have an existing application deployed on many SQL 2000 servers running without issue. We installed SQL 2005 (8 way DualCore) and the most simple trivial SQL statements are taking an extreme amount of time (e.g. an identical query in 2000 took 10 ms is taking a couple of minutes in 2005). The data in the tables being queried are very small (low row count < 400; and low row size < 1k).
When profiling 2005 we see the connection going into a status of 'suspended' with a wait type of 'ASYNC_NETWORK_IO'. There just simply is not enough data being retrieved for a lag to occur returning data to the client. Has anyone seen anything like this? Any thoughts?
I haven't seen anything relating to NETWORK_IO, one gotcha is not doing index rebuilds. http://sqlblogcasts.com/blogs/simons/archive/2006/10/20/SQL-2005-UpgradeRebuild-your-indexes-.aspx
Can you include more details on the queries.
If you run the queries in SSMS do they still take a long time?
How many users are using the server? Is there server doing anything else?
|||
This is a new client of ours (installing our web app, which, has been in production for a few years and we have no issues in house on our 2005 or 2000 SQL boxes) so there are no current active users (save the one that I was using to test with); so the current activity on the server is virtually nill. This is one of our default databases (again, virtually no data except for so default info in the one table I am querying) - all indexes are up to date.
The table has simple varchar columns and two text columns (the data in the columns vary from 100 bytes to 10k [the high end]. Here is teh defintion of the table:
Table [REPORT_DEF]
[REPORT_GU] [uniqueidentifier] NOT NULL,
[NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAMESPACE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NUMBER] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAGE_ORIENTATION] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OWNER_TYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORGANIZATION_GU] [uniqueidentifier] NULL,
[XML_DEF] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CLASS_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSEMBLY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMPANY_GU] [uniqueidentifier] NULL,
[LOCATION] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ICON] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TITLE] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SHORT_TITLE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCALE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORT_VIEW_TYPE] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MODULE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPLOY_VERSION] [numeric](7, 0) NOT NULL DEFAULT ((0)),
[MAIN_REPORT_RPX] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSOCIATED_REPORT_GU] [uniqueidentifier] NULL,
CONSTRAINT PRIMARY KEY CLUSTERED - Column: REPORT_GU
CONSTRAINT UNIQUE NONCLUSTERED - columns: NAMESPACE and NAME
The query that is being run is as follows:
SELECT R0.[COMPANY_GU],R0.[NUMBER],R0.[NAME],R0.[ASSOCIATED_REPORT_GU],R0.[MAIN_REPORT_RPX],
R0.[REPORT_GU],R0.[PAGE_ORIENTATION],R0.[SHORT_TITLE],R0.[ASSEMBLY],R0.[MODULE],R0.[TITLE],
R0.[DEPLOY_VERSION],R0.[NAMESPACE],R0.[LOCATION],R0.[ORGANIZATION_GU],R0.[ICON],R0.[OWNER_TYPE],
R0.[XML_DEF],R0.[LOCALE],R0.[REPORT_VIEW_TYPE],R0.[CLASS_NAME]
FROM [REPORT_DEF] R0
ORDER BY R0.[NAMESPACE] ASC,R0.[NAME] ASC,R0.[REPORT_GU] ASC
Again, when I execute this query on the DB server (via Mgmt Studio) it executes in about 80 ms (give or take a couple ms). When run on a machine on the same network (gigabit backbone, the only two machines on this network) the execution time jumps to 75 seconds (give or take 5 seconds).
Thoughts?
|||Can you run profiler and capture the XML plans, and then compare them, to check they are getting the same plan.
Also be aware that by default SSMS uses the in memory data provider which will give performance improvements compared with running on the client.
How much data is being returned?
Are you using SSMS on the client as well?
|||Simon,
Thanks for your thoughts on this one. I broke down and called MS and after about 6 hours we found that there was a network issue. Both SQL and the web server machines have gigabit NICs but the switch that the client had the two machines plugged into is a 10/100 switch...with the NICs set to autosensing basic network traffic was extremely delayed (tested via explorer copying a 70MB file to the SQL server occured in 8 seconds, from the SQL server to the web sever was taking about 35 minutes). Anyway, chalk this one up to experience and something to look for next time.
Thanks for your help!
No comments:
Post a Comment