Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Urgent help required !!!!! Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2008 4:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:59 AM
Points: 313, Visits: 538
we hav a server in which there is only 1 database from the front end users are getting problem while ftching records ...i hav checked indexes thy are proper ...query is really simple 2 days back it was taking on 2 sec for the out put now it is taking 40 to 60 secs and it is really slow ...
Plz help i m a dba wat shud i check now counter or memory..... plz let me fast ...

Thkz in adv
Post #543317
Posted Wednesday, July 30, 2008 5:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Run a server side trace , have a look at what excatly is casuing the problems and start from there.
Post #543323
Posted Wednesday, July 30, 2008 5:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:59 AM
Points: 313, Visits: 538
this is the query taking time 1st it was taking only 2sec now it takes 40 to 60 secs .... databse size 18 GB ,ram 3 GB
sqlserver taking more then 2 Gb of ram

select alias.id,alias.aaa,alias.colname,table1.id,
table1.ttt,table1.c_f_name,cy.cmd from table3 alias,
cm table1,table2 cy where alias.colname=table1.colname
and cy.colname = table1.colname and table1.id='354'and
alias.colname in (select colname from table2 where colname
like '365464') and colname='A' and colname =123)
Post #543344
Posted Wednesday, July 30, 2008 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:53 AM
Points: 47, Visits: 184
Sorry marfu, but without seeing the structure of your tables, indexes etc it is difficult to see why this could be happening.

How big is the actual table? If you look at the execution plan, you could see which indexes it is using and try to rebuild those first...Providing no-one has deleted an index, then it should just be a case of rebuilding the stats (a reindex will do that for you, so there is no need to run a stats rebuild if you are reindexing) or re-creating the index if it has been deleted - you could try running the query through the engine tuning wizard and build any stats/indexes it recommends.

I wouldn't normally recommend that, but as I can't physically get my hands on the server, its tricky :D


"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
Post #543367
Posted Wednesday, July 30, 2008 6:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
Has anyone dropped or changed an index?
Are any of the indexes fragmented?
Are the statistics up to date?
What does the execution plan of that query show?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #543398
Posted Wednesday, July 30, 2008 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:52 PM
Points: 2, Visits: 72
What's the front end? Does the front end fetch entire tables before completing the qurery or send a few bytes to the back end for processing?

What other network traffic is running?

Dump temp cache in clients and reboot.
Post #543461
Posted Thursday, July 31, 2008 6:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:59 AM
Points: 313, Visits: 538
There is no issue wth indexes but i reboot my database server and it was workin fine ... application is on different server ... users fetch and insert data thro frnt end ....
this is wat i got abut fregmentation...there is no issue wth it..


DBCC SHOWCONTIG scanning tablename' table...
Table: tablename (1093578934); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 245034
- Extents Scanned..............................: 30783
- Extent Switches..............................: 31406
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 97.53% [30630:31407]
- Logical Scan Fragmentation ..................: 0.16%
- Extent Scan Fragmentation ...................: 0.29%
- Avg. Bytes Free per Page.....................: 1499.8
- Avg. Page Density (full).....................: 81.47%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Post #544319
Posted Thursday, July 31, 2008 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:53 AM
Points: 47, Visits: 184
Marfu,
Thats great.
Have you looked at the execution plan to see what indexes the query is using? If it is doing a table scan, then maybe you need to see if someone has dropped an index (I believe Gail said about that earlier).
Have you run the query through the engine tuning wizard - JUST to make sure nothing has been accidentally dropped...


"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
Post #544328
Posted Friday, August 1, 2008 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:08 AM
Points: 43, Visits: 344
Well, I agree with the others to first look at your index. If the index is still there then start with performance monitor to look at the processor counter, batch requests/sec, etc. It may also because the heavier traffic during that day. Another cause is the table may grow larger than before. Or the cache is emptied that makes compiling also possible. Check with the execution plan as well.

Ivan Budiono
Post #544936
Posted Friday, August 1, 2008 4:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
maruf24 (7/30/2008)

select alias.id,alias.aaa,alias.colname,table1.id,
table1.ttt,table1.c_f_name,cy.cmd from table3 alias,
cm table1,table2 cy where alias.colname=table1.colname
and cy.colname = table1.colname and table1.id='354'and
alias.colname in (select colname from table2 where colname
like '365464') and colname='A' and colname =123)


i may be wrong ... but still 2 cents
cant u take away the "in" part and replace it with
colname = '365464'. Another thing using like is also going to return one record.



"Keep Trying"
Post #544959
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse