Database is very slow. Need suggestions

Posted by Skraghava under Sql Server on 7/13/2010 | Views : 4055 | Status : [Member] | Replies : 2
Hi,

My database is very slow. Even a small query of innerjoin which result in 200 rows is taking 6 minutes.

What are the possiblities for which Database is slow and how do i come out of this?

Thanks and regards,
Raghavendra SK



Responses

Posted by: PandianS on: 7/13/2010 [Member] [MVP] Silver

Up
0
Down
Hi Raghavendra

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.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Syedshakeer on: 7/14/2010 [Member] Starter

Up
0
Down
Hi,
This can be solved by creating an Index.
Index should be created on where condition column name.
for eg:
select column1,column2 from tablename where id=101

creating Index
Create index id_index
on tablename(id)


here i created INDEX on id column..

2)mention only that Columns in a SqlQuery what you need...
dnt write query as select * from tablename where id=101

write query as select column1,column2 from tablename where id=101

Syed Shakeer Hussain

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response