Please come up with the following info., After that you will have clear understanding in which area you have to tune.
1. Which SQL Server Edition and Version you using ?
2. Your total Hard Disk capasity.( Each Drive wise) and Free available space on each drive ?
3. In which drive you have Data file and Log File of your database (Which is slow database) ?
4. In which drive you have TempDB database ?
5. What about the Initial size of your TempDB Database ?
6. What about the Growth size(In Percentage or MB) of your TempDB Database ?
7. How many tables are there in your Database ?
8. How many Filegroups you have in your Database ?
9. In Each Filegroup, How many tables you have ?
10. What about the Initial size of your Database ?
11. What about the Growth size(In Percentage or MB) of your Database ?
12. Do you using any Partitions ?
13. If so, Give detail info ( Filegroups, Partition Type, Range,...Etc)
14. What about Fragmentation level of each Tables/Indexes ?
15. What about Statistics on each Column/Tables ?
16. Do you have scheduled any maintenance plan / Job ?
17. Whevever you execute a Query, What kind of "Wait Type" held ?
18. What about your network bandwidth ?
19. Whenever you using SQL Server(for each activity), How your DB Server's Processor, Memory behaving(High/Low) ?
20. How many records you have on your each Tables which is used in your JOINs.
21. We need Execution plan for the slow running queries with statistics.
22. Do you using any Read-Only filegroup on your database ? If so, How many and What are the tables you have on that Filegroup ?
These are the Basic considerations on Slow running queries/Database objects/Databases.
CheersSkraghava, if this helps please login to Mark As Answer. | Alert Moderator