July 30, 2008 at 4:53 am
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
July 30, 2008 at 5:08 am
Run a server side trace , have a look at what excatly is casuing the problems and start from there.
July 30, 2008 at 5:33 am
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)
July 30, 2008 at 6:03 am
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 😀
"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
July 30, 2008 at 6:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2008 at 7:32 am
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.
July 31, 2008 at 6:34 am
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.
July 31, 2008 at 6:42 am
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
August 1, 2008 at 3:32 am
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
August 1, 2008 at 4:17 am
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"
August 1, 2008 at 4:38 am
Chirag,
I think colname is just an alias to keep the fields anonymous 🙂
"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
August 1, 2008 at 5:14 am
Are any of your tables growing?
You have an inline view on which itself has a like comparison... Neither of those are helping.
If tables referenced in main query are growing that means you are processing more times the inline view.
If table referenced in the inline view is growing that probably means your like operation is analyzing more rows.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 6:30 am
defrag your computer
August 1, 2008 at 9:18 am
1) IN clauses can be performance dogs. Try to rewrite query using EXISTS.
2) Post the actual execution plan and the IOs used here for review. We will need table counts too, although it certainly looks like a big table based on page count from frag report.
3) Didn't notice, but did you verify that no one dropped any indexes?
4) run update statistics on tables involved in query
5) consider dbcc freeproccache. Note this will cause a (possibly severe) server slowdown for a while as query plans are recached. Actually, before doing this execute the query with OPTION (RECOMPILE). Please do all steps above before this step.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 1, 2008 at 1:27 pm
GilaMonster (7/30/2008)
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?
x2
Can you do a DB compare?
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply