Slow response
in [Programming SQL]
|
From: TheSQLGuru on 27 Feb 2010 21:59 do a web search for parameter sniffing. Cached query plans can be very good for some parameter values and horrible for others. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Samuel" <smshulman(a)hotmail.com> wrote in message news:%235D1yaltKHA.4568(a)TK2MSFTNGP05.phx.gbl... > Hi > > I send to the database the same query for many records but only for some > it reposes very slowly > > There is no apparent explanation as there is no large amount of data. > > It is so slow that I had to raise the timeout from 30 to 120 > > Can anyone please suggest any reason. Is it file corruption (I backed up > and restored the database) > > Thank you in advance, > Samuel >
From: sloan on 5 Mar 2010 10:25 //Ordering hits tempdb for a sort operation.// Kevin, Is that documented anywhere? I had this discussion with some other developers and one "dba" in our company. (Note, I do not claim to be a dba). Because I had made a practice of not ordering-by in the database, and did some basic sorting in the middle-tier (where we have 3 or 4 or N number of machines). I'm talking basic stuff, order by lastname, firstname. My theory was "let's offload as much as we can from the db, even if its a tidbit". But basically, I was questioned at length, because the easy answer was slap a Order By to the end of everything coming out of the db. Note, the database is getting hit by 600 to 700 ~~concurrent users. (Not "total" users). Think "HelpDeskTicket" system with people responding to HelpDeskTickets all day, everyday. "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:y-qdnZnGwMKKRhTWnZ2dnUVZ_hudnZ2d(a)earthlink.com... > Ordering hits tempdb for a sort operation. this can be very slow > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Samuel" <smshulman(a)hotmail.com> wrote in message > news:%23pwo3QmtKHA.3536(a)TK2MSFTNGP06.phx.gbl... >>I located the problem though I cannot explain it. >> >> When I specify ORDRE BY TableA.FieldA it slows down dramatically. I do >> not use a stored procedure >> >> Thank you >> >> >> >> >> "Samuel" <smshulman(a)hotmail.com> wrote in message >> news:%235D1yaltKHA.4568(a)TK2MSFTNGP05.phx.gbl... >>> Hi >>> >>> I send to the database the same query for many records but only for some >>> it reposes very slowly >>> >>> There is no apparent explanation as there is no large amount of data. >>> >>> It is so slow that I had to raise the timeout from 30 to 120 >>> >>> Can anyone please suggest any reason. Is it file corruption (I backed up >>> and restored the database) >>> >>> Thank you in advance, >>> Samuel >>> >> >> > >
From: Eric Russell on 6 Mar 2010 12:55 SQL Server will use tempdb to store the intermediate result of an order by, especially if you're sorting a large resultset and the ordered columns are not covered by an index. Analyze the execution plan of the query to determine how many rows are typically involved in these sort operations, and also refer to this MSDN article which describes how to monitor tempdb usage. Working with tempdb in SQL Server 2005 http://msdn.microsoft.com/en-us/library/cc966545.aspx By default, I think the arrangement should be that the application or middle tier sorts the resultset, especially if it's a client side application. Only if the architect pushes back with a technical reason for sorting on the database side should you include an ORDER BY clause in your queries. For example, if you ask a librarian to pull some reference material off the shelf, they'll expect you to take the books back to your desk and sort them yourself, which makes sense if there are several more people waiting in line behind you. "sloan" <sloan(a)ipass.net> wrote in message news:Oq5POgHvKHA.800(a)TK2MSFTNGP04.phx.gbl... > //Ordering hits tempdb for a sort operation.// > > > Kevin, > > Is that documented anywhere? > > I had this discussion with some other developers and one "dba" in our > company. (Note, I do not claim to be a dba). > Because I had made a practice of not ordering-by in the database, and did > some basic sorting in the middle-tier (where we have 3 or 4 or N number of > machines). I'm talking basic stuff, order by lastname, firstname. My > theory was "let's offload as much as we can from the db, even if its a > tidbit". > But basically, I was questioned at length, because the easy answer was > slap a Order By to the end of everything coming out of the db. > > Note, the database is getting hit by 600 to 700 ~~concurrent users. (Not > "total" users). Think "HelpDeskTicket" system with people responding to > HelpDeskTickets all day, everyday. > > > > > > "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message > news:y-qdnZnGwMKKRhTWnZ2dnUVZ_hudnZ2d(a)earthlink.com... >> Ordering hits tempdb for a sort operation. this can be very slow >> >> -- >> Kevin G. Boles >> Indicium Resources, Inc. >> SQL Server MVP >> kgboles a earthlink dt net >> >> >> "Samuel" <smshulman(a)hotmail.com> wrote in message >> news:%23pwo3QmtKHA.3536(a)TK2MSFTNGP06.phx.gbl... >>>I located the problem though I cannot explain it. >>> >>> When I specify ORDRE BY TableA.FieldA it slows down dramatically. I do >>> not use a stored procedure >>> >>> Thank you >>> >>> >>> >>> >>> "Samuel" <smshulman(a)hotmail.com> wrote in message >>> news:%235D1yaltKHA.4568(a)TK2MSFTNGP05.phx.gbl... >>>> Hi >>>> >>>> I send to the database the same query for many records but only for >>>> some it reposes very slowly >>>> >>>> There is no apparent explanation as there is no large amount of data. >>>> >>>> It is so slow that I had to raise the timeout from 30 to 120 >>>> >>>> Can anyone please suggest any reason. Is it file corruption (I backed >>>> up and restored the database) >>>> >>>> Thank you in advance, >>>> Samuel >>>> >>> >>> >> >> > >
First
|
Prev
|
Pages: 1 2 Prev: Chart Question Next: Updating column values only if value is not null |