473,231 Members | 1,640 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 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 1894
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Don | last post by:
I have MSSQL2k SP3a on WIN2k SP4. moved a Date/log files to this server about a week ago from a SQL7 server and attached it to this new Sql2k server. everything works fine for about 24hrs and...
14
by: Kukurydz | last post by:
I've got such problem: My database is stored on MSSQL Server. I have to write reports for it in MSAccess. I've got a problem with creating a query which will select records from MSSQL table with...
2
by: Ian Hinson | last post by:
Hi, Sometimes I find it handy to construct an sql string for action queries, and then run it directly against the database and/or connection instead of using Recordsets for simple updates. eg....
2
by: Jeremy Cowles | last post by:
Disclaimer: This could be considered an ADO question, but, it really is a question of code maintenance. I have created a utility app that synchronizes MSSQL tables & stored procedures as Classes...
11
by: ralphie | last post by:
hi all since nearly 2 days i fight with mssql and utf-8 as i need to store and retrieve arabic characters. i tried the com approach ...
8
by: No bother | last post by:
I have a table with two columns, one named master, the other slave. Each column has a set of numbers. A number in one column can appear in the other. I am trying to see if there is any infinite...
2
by: larmores | last post by:
I have a medical database being exported to fixed-width text file and then need to ftp. I have a data source and can extract 6 of the seven tables via a Transform Data Task connected to a Text File...
14
by: guswebb | last post by:
Hi. I'm a newbie to PHP and am having a few problems as follows... I have installed PHP successfully on server 1 which is running IIS 6 (W2k3) and hosting multiple sites, some of which connect to...
11
by: Icemokka | last post by:
Hi, I'm need to upload a big file ( 600Mb+ ) to a BLOB field in MSSQL 2005. My code looks like this : fs = New FileStream(sFilePath, FileMode.Open) Dim ByteArray(fs.Length) As Byte...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.