|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server Small Table Performance Problem
and one user SQL Server 2005. I tracked both issues down the a single query as follows: "select count(*) from statustable" This table typically has 0-150 records in it. Never more than that. It stores what users are currently logged into the system. When a user logs in, I insert a record. When the user logs out, I delete the record. So this table constantly has records being inserted and deleted from it. The profiler shows the query taking 0 ms in most cases, but sometimes it spikes to 900 seconds (yes, 15 minutes). The users experience a freeze-up while this is running. No other queries are affected. Also, I didn't see any queries blocking this one. None of the other 15000 queries that fire for the day takes more than 300 ms. Are there issues to a table getting "thrashed"? Since we are not talking about a lot of table transactions per day (500 max), I would hope not, but I can't explain the spikes. Any ideas? To better understand this issue, tell me more about this little table: could
you post the design of the table (e.g. fields, data types, KEYS and indexes) and the consistency of your updates (which information you insert for each logged on user). Gilberto Zampatti Show quoteHide quote "Isaac Alexander" wrote: > I two customers reporting a performance problem. One using SQL Server 2000 > and one user SQL Server 2005. > > I tracked both issues down the a single query as follows: "select count(*) > from statustable" > > This table typically has 0-150 records in it. Never more than that. It > stores what users are currently logged into the system. When a user logs > in, I insert a record. When the user logs out, I delete the record. > > So this table constantly has records being inserted and deleted from it. > > The profiler shows the query taking 0 ms in most cases, but sometimes it > spikes to 900 seconds (yes, 15 minutes). The users experience a freeze-up > while this is running. No other queries are affected. Also, I didn't see > any queries blocking this one. None of the other 15000 queries that fire > for the day takes more than 300 ms. > > Are there issues to a table getting "thrashed"? Since we are not talking > about a lot of table transactions per day (500 max), I would hope not, but I > can't explain the spikes. > > Any ideas? > > > "Gilberto Zampatti" <GilbertoZampa***@discussions.microsoft.com> wrote in Here is the table structure.message news:21F932AA-20D7-4BB3-969D-DEAFEDFD3DF2@microsoft.com... > To better understand this issue, tell me more about this little table: > could > you post the design of the table (e.g. fields, data types, KEYS and > indexes) > and the consistency of your updates (which information you insert for each > logged on user). CREATE TABLE dbo.USERSTATUS( STATUS_ID varchar(14) NOT NULL, USER_ID varchar(14) NOT NULL, USRMESSAGE varchar(80) NULL, LOGDATE datetime NULL, CHECKDATE datetime NULL, TEMPUSER varchar(1) NULL DEFAULT ('F'), CURRLOC varchar(80) NULL, ADDINFO varchar(255) NULL, ADMINUSER varchar(1) NULL DEFAULT ('F'), CONSTRAINT PK_USERSTATUS PRIMARY KEY CLUSTERED ( STATUS_ID ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY ) ON PRIMARY When a user logs in, a record is added (most fields are populated). When a user logs out, the record is deleted. On 23.04.2007 17:45, Isaac Alexander wrote:
Show quoteHide quote > "Gilberto Zampatti" <GilbertoZampa***@discussions.microsoft.com> wrote in Any additional indexes? What kinds of queries do they run?> message news:21F932AA-20D7-4BB3-969D-DEAFEDFD3DF2@microsoft.com... >> To better understand this issue, tell me more about this little table: >> could >> you post the design of the table (e.g. fields, data types, KEYS and >> indexes) >> and the consistency of your updates (which information you insert for each >> logged on user). > > Here is the table structure. > > CREATE TABLE dbo.USERSTATUS( > STATUS_ID varchar(14) NOT NULL, > USER_ID varchar(14) NOT NULL, > USRMESSAGE varchar(80) NULL, > LOGDATE datetime NULL, > CHECKDATE datetime NULL, > TEMPUSER varchar(1) NULL DEFAULT ('F'), > CURRLOC varchar(80) NULL, > ADDINFO varchar(255) NULL, > ADMINUSER varchar(1) NULL DEFAULT ('F'), > CONSTRAINT PK_USERSTATUS PRIMARY KEY CLUSTERED > ( > STATUS_ID ASC > )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY > ) ON PRIMARY > > When a user logs in, a record is added (most fields are populated). When a > user logs out, the record is deleted. robert > Only one more index on foreign key.> Any additional indexes? What kinds of queries do they run? > CREATE NONCLUSTERED INDEX USERSTATUS_FK1 ON dbo.USERSTATUS (USER_ID ASC) The most active query is... select sum(case adminuser when 'F' then 1 else 0 end) totalusers, sum(case adminuser when 'T' then 1 else 0 end) totaladminusers from userstatus where tempuser = 'F' The second most just brings back a total count of records. I didn't add indexes to TEMPUSER or ADMINUSER because I though that when there can only be a couple values in the table, it wasn't worth it. I could be wrong. If you have no index on tempuser, then this query will require a table
lock. So any insert, update or delete to this table will have to wait for the lock to be released. In other words, adding an index on tempuser is likely to reduce blocking quite a lot. The existence of an index on adminuser is not very relevant here. Gert-Jan Isaac Alexander wrote: Show quoteHide quote > > > > > Any additional indexes? What kinds of queries do they run? > > > > Only one more index on foreign key. > > CREATE NONCLUSTERED INDEX USERSTATUS_FK1 ON dbo.USERSTATUS (USER_ID ASC) > > The most active query is... > > select sum(case adminuser when 'F' then 1 else 0 end) totalusers, > sum(case adminuser when 'T' then 1 else 0 end) totaladminusers > from userstatus > where tempuser = 'F' > > The second most just brings back a total count of records. > > I didn't add indexes to TEMPUSER or ADMINUSER because I though that when > there can only be a couple values in the table, it wasn't worth it. I could > be wrong. "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message Thanks. I'll add the index and the nolocks.news:462D0148.8AC20700@toomuchspamalready.nl... > If you have no index on tempuser, then this query will require a table > lock. So any insert, update or delete to this table will have to wait > for the lock to be released. In other words, adding an index on tempuser > is likely to reduce blocking quite a lot. > > The existence of an index on adminuser is not very relevant here. > On 21.04.2007 01:38, Isaac Alexander wrote:
Show quoteHide quote > I two customers reporting a performance problem. One using SQL Server 2000 Memory configuration: make sure you've configured less than physically > and one user SQL Server 2005. > > I tracked both issues down the a single query as follows: "select count(*) > from statustable" > > This table typically has 0-150 records in it. Never more than that. It > stores what users are currently logged into the system. When a user logs > in, I insert a record. When the user logs out, I delete the record. > > So this table constantly has records being inserted and deleted from it. > > The profiler shows the query taking 0 ms in most cases, but sometimes it > spikes to 900 seconds (yes, 15 minutes). The users experience a freeze-up > while this is running. No other queries are affected. Also, I didn't see > any queries blocking this one. None of the other 15000 queries that fire > for the day takes more than 300 ms. > > Are there issues to a table getting "thrashed"? Since we are not talking > about a lot of table transactions per day (500 max), I would hope not, but I > can't explain the spikes. > > Any ideas? available minus some slack for OS and disk cache. Othewise your DB might start swapping which almost always kills performance. robert > Memory configuration: make sure you've configured less than physically The memory looks good. Their database is fairly small (less than 1 GB).> available minus some slack for OS and disk cache. Othewise your DB might > start swapping which almost always kills performance. > 1) When you get report of slowness, run sp_who2 active from QA/SSMS. Check
the BlkBy column. My guess is that one (or more) of the INSERTs/DELETEs are blocking each other. You could even be getting a DEADLOCK if you aren't controlling your transactions tightly. Sorry, just reread your post and noticed that you state no blocking. Ok then, check for LOCK Waits (see BOL) to see if a resource isn't available. 2) Can you use a NOLOCK in your count select? SELECT COUNT(*) FROM StatusTable (NOLOCK) Note that this will no longer 'guarantee' getting the exact number since there could be an uncommitted INSERT/DELETE you will skip over in the read. 3) Are you certain that it is necessary to track users in this manner? Can you remove this from the code? If you can't, would it be possible to reference a system table to see who/how many users are logged in? -- Show quoteHide quoteTheSQLGuru President Indicium Resources, Inc. "Isaac Alexander" <isaacNOSPAM@goNOSPAMprocura.com> wrote in message news:OyvS%23R6gHHA.3412@TK2MSFTNGP02.phx.gbl... >I two customers reporting a performance problem. One using SQL Server 2000 >and one user SQL Server 2005. > > I tracked both issues down the a single query as follows: "select count(*) > from statustable" > > This table typically has 0-150 records in it. Never more than that. It > stores what users are currently logged into the system. When a user logs > in, I insert a record. When the user logs out, I delete the record. > > So this table constantly has records being inserted and deleted from it. > > The profiler shows the query taking 0 ms in most cases, but sometimes it > spikes to 900 seconds (yes, 15 minutes). The users experience a freeze-up > while this is running. No other queries are affected. Also, I didn't see > any queries blocking this one. None of the other 15000 queries that fire > for the day takes more than 300 ms. > > Are there issues to a table getting "thrashed"? Since we are not talking > about a lot of table transactions per day (500 max), I would hope not, but > I can't explain the spikes. > > Any ideas? > "TheSQLGuru" <kgbo***@earthlink.net> wrote in message I don't have direct access to the customer's databases so I have to rely on news:%23Yl3LwOhHHA.4032@TK2MSFTNGP02.phx.gbl... > 1) When you get report of slowness, run sp_who2 active from QA/SSMS. > Check the BlkBy column. My guess is that one (or more) of the > INSERTs/DELETEs are blocking each other. You could even be getting a > DEADLOCK if you aren't controlling your transactions tightly. > > Sorry, just reread your post and noticed that you state no blocking. Ok > then, check for LOCK Waits (see BOL) to see if a resource isn't available. their IT team to run queries. I'll get them to check. > That was the first solution I thought about. I was just curious how a table > 2) Can you use a NOLOCK in your count select? > > SELECT COUNT(*) FROM StatusTable (NOLOCK) > > Note that this will no longer 'guarantee' getting the exact number since > there could be an uncommitted INSERT/DELETE you will skip over in the > read. with so few records could be performing so slowly. I will add the NOLOCKs. > I thought about that as well. The problem I have is that I want to be able > 3) Are you certain that it is necessary to track users in this manner? > Can you remove this from the code? If you can't, would it be possible to > reference a system table to see who/how many users are logged in? > > -- to see more information for each user such as the window that they are currently on, version numbers, IP address, etc. On Fri, 20 Apr 2007 16:38:42 -0700, "Isaac Alexander"
<isaacNOSPAM@goNOSPAMprocura.com> wrote: >So this table constantly has records being inserted and deleted from it. Maybe it's ... the Ghost!?Do you have other tables as well with no clustered keys? Try adding a clustered integer identity field. I know you don't want or need it. The ghost does show up on sp_who2, see if it's present when you get one of those long runs. J.  
Other interesting topics
|
|||||||||||||||||||||||
