June 26, 2012 at 6:28 am
We are getting slowness issue with application, which was store in SAN Drive
SAN Dive total size:975 GB application Size: 45 GB SAN Drive free space:930
My quesion is: Can we store the application in Drive with more free space?
is it causes slowness of application?
we are getting comlaints from users as they are facing slowness issue with this application daily?
June 26, 2012 at 7:45 am
what do you mean by application?
YourBusinessApp.exe, or do you mean the database the app connects to?
performance typically has a lot more to do with the queries being performed, and the amount of data returned, rather than where on a network an executable actually exists. those kinds of perforance things we can offer suggestions for,
this is my typical punch list i go through before i ever start looking at hardware problems:
· Are there clustered indexes on all the tables invloved?
· are there indexes to support the query?
· Is There a WHERE statement being used? if not...that's a table scan of all the data.
· are there columns that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)
· Are the statistics up to date?
· Are you using catch all queries?
· Are the WHERE statement parameters SARG-able?
· Are any funcitons being used in the WHERE statement?
· Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)
· Could the Indexes benefit from INCLUDE columns?
if you can provide more details, we can offer suggestions.
Lowell
June 27, 2012 at 2:14 am
Thanks for your Reply
Yes, that appllication BusinessApp.exe and its connected to database
and Network Executable exists on SAN Drive (Active/Passive cluster)
answers for your questions....
· Are there clustered indexes on all the tables invloved?
Yes
· are there indexes to support the query?
Yes
· Is There a WHERE statement being used? if not...that's a table scan of all the data.
Yes where statements also used
· are there columns that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)
No Only used columns
· Are the statistics up to date?
Yes
· Are you using catch all queries?
I doesn't this quesion, can you please explay briefly
· Are the WHERE statement parameters SARG-able?
I doesn't this quesion, can you please explay briefly
· Are any funcitons being used in the WHERE statement?
i don't have idea, but i will confirm with our users
· Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)
No we will use only multiple joins and select, insert statements Some times cursors
· Could the Indexes benefit from INCLUDE columns?
Yes
and also BusinessAPP.exe Connected to Production Server (Primary Server), Reporting applicatio is connected to Standby(Sencondary Server).
Primary Server included in Windows Clusters
June 28, 2012 at 7:55 am
I want you to tell me EXACTLY what makes up the drive that windows sees that holds your database, and what else might be on the same spindle(s) on the SAN that house that drive. If you cannot do this then you have no hope of being able to properly place your database on the SAN, or know if the SAN is even involved in the performance problem.
You also need to do a file IO stall and wait stats analysis. Those will help you find reasons for slowness in an aggregate sense.
Oh, and download and use sp_whoisactive from sqlblog.com. That will pinpoint right-now problems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply