July 29, 2008 at 1:26 pm
I've got a shiny new PowerEdge 2950 with dual quad cores and 16 GB RAM running Win2K3 R2 SP2 x64 and SQL 2005 Standard. Long story short, simply declaring a table variable takes 2 seconds and I cannot figure out why.
I've moved tempdb to dedicated drives. The CPU is barely breaking a sweat. The drives are showing between 95-98% idle. I've looked at performance monitor counter values and nothing obvious sticks out.
Can anyone suggest where else I could look to figure out why this is happening?
July 29, 2008 at 1:41 pm
July 29, 2008 at 1:55 pm
What specific counters have you looked at?
DAB
July 29, 2008 at 2:17 pm
Good thought on the memory...OpenManage doesn't show any hardware level errors and there is nothing in either the Windows Event Log or the SQL Error Logs.
FWIW here's the statement:
DECLARE @qWordItems1 TABLE (item_id INT)
Some more information...this happens no matter what database I am in. Creating any other kind of object (e.g. a temporary table) does not exhibit the same symptoms. I've profiled the statement when I run it in the master database and I see the following events: First there's a SQLTransaction event in tempdb for 610 ms with "Commit" EventSubClass, then a SQL:BatchCompleted event for 611 ms, and finally a another SQLTransaction event in master for 1340 ms with another "Commit" EventSubClass. This is standard stuff though; I see the same events on my other servers when i run the statement but with no measurable duration.
I'm grabbing the following perfmon counters:
MEMORY | AVAILABLE MBYTES
MEMORY | PAGES INPUT/SEC
MEMORY | PAGES/SEC
NETWORK INTERFACE | BYTES TOTAL/SEC | BROADCOM BCM5708C NETXTREME II GIGE [NDIS VBD CLIENT]
PAGING FILE | % USAGE | _TOTAL
PAGING FILE | % USAGE PEAK | _TOTAL
PROCESS | % PROCESSOR TIME | SQLSERVR
PROCESSOR | % PRIVILEGED TIME | _TOTAL
PROCESSOR | % PROCESSOR TIME | _TOTAL
PROCESSOR | % USER TIME | _TOTAL
SQLSERVER:ACCESS METHODS | FORWARDED RECORDS/SEC
SQLSERVER:ACCESS METHODS | FULL SCANS/SEC
SQLSERVER:ACCESS METHODS | INDEX SEARCHES/SEC
SQLSERVER:ACCESS METHODS | PAGE SPLITS/SEC
SQLSERVER:ACCESS METHODS | TABLE LOCK ESCALATIONS/SEC
SQLSERVER:BUFFER MANAGER | CHECKPOINT PAGES/SEC
SQLSERVER:BUFFER MANAGER | FREE LIST STALLS/SEC
SQLSERVER:BUFFER MANAGER | FREE PAGES
SQLSERVER:BUFFER MANAGER | LAZY WRITES/SEC
SQLSERVER:BUFFER MANAGER | PAGE LIFE EXPECTANCY
SQLSERVER:BUFFER MANAGER | PAGE LOOKUPS/SEC
SQLSERVER:BUFFER MANAGER | PAGE READS/SEC
SQLSERVER:BUFFER MANAGER | PAGE WRITES/SEC
SQLSERVER:DATABASES | TRANSACTIONS/SEC | _TOTAL
SQLSERVER:GENERAL STATISTICS | USER CONNECTIONS
SQLSERVER:LATCHES | LATCH WAITS/SEC
SQLSERVER:LATCHES | TOTAL LATCH WAIT TIME (MS)
SQLSERVER:LOCKS | LOCK WAIT TIME (MS) | _TOTAL
SQLSERVER:LOCKS | LOCK WAITS/SEC | _TOTAL
SQLSERVER:LOCKS | NUMBER OF DEADLOCKS/SEC | _TOTAL
SQLSERVER:MEMORY MANAGER | MEMORY GRANTS PENDING
SQLSERVER:SQL STATISTICS | BATCH REQUESTS/SEC
SQLSERVER:SQL STATISTICS | SQL COMPILATIONS/SEC
SQLSERVER:SQL STATISTICS | SQL RE-COMPILATIONS/SEC
SYSTEM | CONTEXT SWITCHES/SEC
SYSTEM | PROCESSOR QUEUE LENGTH
July 29, 2008 at 3:07 pm
This is interesting. I created some table variables and looked at some counters. The FIRST time I created a table variable (a small one declare @t table (myI int) ) it took 3 seconds to create. After that, whammo. Almost immediately.
DAB
July 30, 2008 at 4:26 am
Just try it out with ordinary or temporary table creation
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 30, 2008 at 4:34 am
Hi
no such issues for me. r u doing this inside a proc.
"Keep Trying"
July 30, 2008 at 7:30 am
July 30, 2008 at 7:42 am
Again, quite the opposite results on my system. After a reboot, first declaration took about 3 seconds. After that, they declare instantly.
DAB
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply