July 22, 2012 at 6:24 pm
SQL 2008 backend in Server 2008 - MS-Access 2010 frontend in workstations.
No significant changes in the database as well as the frontend program.
Performace deterioted sudendly (ex: query in the frontend was working fast 1sec-for the same query and conditions now is taking 6 sec.)
Any recommendations?
Thanks
Norbert
July 22, 2012 at 7:17 pm
Asking what to look at when a query is slow is a bit like asking what colour string to use to tie up a balloon - it highly depends on your circumstances and nobody can really answer it for you without knowing what colour your balloon is and whether or not you like polka-dots.
A query is not going to slow down for no reason - something will have changed with the system and that change may not necessarily be in SQL Server or the database. It could be a change to the I/O load on the disk subsystem, CPU's over-taxed by other processes on the server, network latency for example.
I'd generally put the changes into a few categories and the plan of attack you would take would depend on what has changed.
1.) SQL Server no longer has access to the same amount of resources as previous - eg something else is using up more resources than before.
2.) SQL Server now required more resources as it did previously - more databases/users have been added, larger data sets, more indexes etc.
3.) The information the query optimizer has at its disposal to generate an efficient plan for satisfying the query is outdated/missing - eg old statistics, insufficient indexing.
If you don't have any idea what has changed on ther server I would start with locating the plan in the plan cache - looking at the sys.dm_Exec_query_stats DMV and see what worker times, io etc is being experienced - bear in mind this as with most other DMV's is accumulative, so you'll need to look at the figures before running the query, then run it and look at the figures again and see what the change is - assuming you can isolate the system from other users so you don't get a skewed result. You would probably know roughly how much I/O etc your query would need based on how much data you know it to be fetching, so if it looks like it's reading a lot more I/O than necessary you might need to optimize it. Additionally if it's doing a lot more physical I/O than logical you need to look at whether you have pressure on your buffer cache that's causing data to not be kept in memory.
You could also look at the wait stats (sys.dm_os_wait_stats DMV) before and after your query runs and see what waits were experienced - again by seeing which waits changed. If the waits are I/O related for example it could point to bottlenecks in your I/O system or something else over-taxing your disks. If they are memory related then again you might have problems with not enough memory allocated to the buffer cache. If they are CPU related or you have high signal wait times then your processesors might be getting a whacking.
Check the query plan using sys.dm_Exec_text_query_plan and see if you can spot an inefficiency in the plan being generated due to missing indexes or outdated statistics. (Alternatively run the query yourself in SSMS and select the display actual execution plan button to show you a graphical plan)
Just a few tips to start...
July 22, 2012 at 7:25 pm
July 22, 2012 at 7:27 pm
Hi Thanks for your wonderful reply.
I just intalled the programs in just a computer and the same conditions are showing.
As you can suspect I am not to familiar with SQL but I managed to troubleshoot the best way I know.
If I restore an older database (has 385Mb) then the sample query works however I restored again the actual database (415 Mb) in my computer and the query slows down. I did not change anything in the design.
Also I always shrink and reorganize files.
July 23, 2012 at 1:07 am
drnorbert (7/22/2012)
Also I always shrink and reorganize files.
Well there's part of the problem. 🙂
Don't shrink. SQL's not like Access where the files have to be regularly shrunk in order for them not to go corrupt. In SQL, shrink should be a once-off operation after an unusual operation (major archive, huge data delete, etc)
Stop shrinking the DB.
You may find this useful: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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 23, 2012 at 5:38 am
uh Access linked tables ALWAYS provide sub-standard performance. You should move to Access Data Projects- there are a lot less layers between Access in SQL Server if you use ADP.
I just think that Access linked tables is ALWAYS an awful architecture.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply