471,571 Members | 1,040 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,571 software developers and data experts.

MSSQL Query Memory when using long fields

Here is our problem...

We are doing a lot of selects against a table that has one large field
in it.

If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)

When we run the one without the description column, we can watch
perfmon and see a very quick spike to physical disk. If we add in the
description field we can see that the server becomes I/O bound - the
disk sits at 100% until the query is complete.

We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?
For example:
TMZDIFF int 4 10
WRITETIME char no 16
System_Name char no 64
Timestamp char no 16
Name char no 32
Mount_Point char no 32
Size int no 4 10
Space_Used int no 4 10
Space_Available int no 4 10
Inode_Size int no 4 10
Inodes_Used int no 4 10
Inodes_Free int no 4 10
Space_Used_Percent int no 4 10
Inodes_Used_Percent int no 4 10
FS_Type char no 8
Space_Available_Percent int no 4 10
Name_U nchar no 32
Description nchar no 768

Mar 2 '06 #1
2 1801
(wi***********@yahoo.com) writes:
If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)
My initial reaction is that there is an index on the table that covers
all columns, save the long column.

Could you post CREATE TABLE and CREATE INDEX statements for the table?
Don't forget PRIMARY KEY and UNIQUE constraints.
We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?


How many rows are you returning? On what sort of network connection?
How much memory do you have in the machine?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 3 '06 #2
give us an example of the two selects.

Mar 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Don | last post: by
14 posts views Thread by Kukurydz | last post: by
2 posts views Thread by Ian Hinson | last post: by
2 posts views Thread by Jeremy Cowles | last post: by
11 posts views Thread by ralphie | last post: by
14 posts views Thread by guswebb | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.