?
Solved

SQL query time

Posted on 2011-09-13
18
Medium Priority
?
319 Views
Last Modified: 2012-08-14
We have a custom app written in ASP.NET 3.5 and C+ that we interface via Internet Explorer on our desktops. It uses a SQL database on our SBS 2008. Some of the queries take 30-60 seconds and I would like to speed that up.  One option I am looking at is to change over to a SSD on on the server, as I feel the RAID 5 is contributing to the slowness.  Any SQL experts want to give input on what I should look at, whether to upgrade to an SSD or whether to have a look at the SQL code?
0
Comment
Question by:rodynetwork
  • 9
  • 5
  • 2
  • +2
18 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 36529593
Have you looked at the indexes that have been created or do indexes exist?  
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 500 total points
ID: 36529607
Run the suspect queries in SSMS with Include Actual Execution Plan turned on.  In 2008, the execution plan will actually recommend missing indexes if it thinks that some are needed.  If there are no missing indexes, do you rebuild your indexes on a regular basis?  Rebuilding indexes will defrag your data and more importantly update your statistics which is what SQL Server uses to determine the best execution plan.

Greg

0
 

Author Comment

by:rodynetwork
ID: 36530218
I know nothing about SQL, but I want to learn.  Can you walk me through how to run the queries in SSMS with Include Actual Execution Plan?  I have SSMS up and have the Object Explorer-server expanded.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rodynetwork
ID: 36530242
Actually, what I may do is set up a second SBS 2008 and practice on it.  Do you think it is realistic to get input from EE experts for that?  I am thinking of setting up the second server and learning by doing.  I figure I can use Go to Meeting for allowing experts to show me things that are difficult to convey in posts.
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 500 total points
ID: 36535163
use SQL Server tunning advisor to take  advice , where to create a index to improve the performance
0
 

Author Comment

by:rodynetwork
ID: 36536389
Thanks for the tip.  Where is the tuning advisor located?
0
 

Author Comment

by:rodynetwork
ID: 36537301
In SSMS, under tools, I am finding "external tools, customize, options". I don't see Tuning Advisor in any of those.  Where do I find it?
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36537753
In SSMS, it's under Tools --> Database Engine Tuning Advisor.  But when you run it, you need a query or stored procedure to tune.  You can also find it if you right click on the query window.

Greg

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36540374
>>I don't see Tuning Advisor in any of those.  Where do I find it? <<
What Edition do you have?

SELECT SERVERPROPERTY('Edition')
0
 

Author Comment

by:rodynetwork
ID: 36542842
I can't find "server properties".  I attached a pic. Does that help?  Sorry, I am almost completely ignorant at this point with SQL.     1
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36543491
>>I can't find "server properties".<<
1. Click on the big button on the top left corner that reads "New Query".
2. Type in the new window: SELECT SERVERPROPERTY('Edition')
3. Press F5
4. Post the output.

>>Sorry, I am almost completely ignorant at this point with SQL.   <<
It may be time to crack some books, so that you can at least get the basics.
0
 

Author Comment

by:rodynetwork
ID: 36543562
what books do you recommend? Or, can you recommend a starting point?  It is hard to know where to start....
0
 

Author Comment

by:rodynetwork
ID: 36543573
cool.   Standard Edition (64 bit)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36544851
>>what books do you recommend?<<
I would go on Amazon and find an entry level book on the subject. You also have SQL Server BOL available to you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36544876
While you have the query window open, you should find the "Database Engine Tuning Advisor" under "SQL Server Profiler" in the Tools menu.
0
 

Author Comment

by:rodynetwork
ID: 36544983
Sorry, I feel an idiot, so posting a pic. Can't find that anywhere.... 2 2
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 36546470
I have no idea why you do not have it available.  I thought even the Standard Edition supported it.  I suspect you do not have the appropriate permissions.  Apparently you have to have the sysadmin server role the first time you start it.  After it has been initialized, then db_owner database role is all that is required.  But here is a tutorial on how to use it:
http://msdn.microsoft.com/en-us/library/ms166575(v=SQL.90).aspx
0
 

Author Closing Comment

by:rodynetwork
ID: 36553960
I haven't been able to find Tuning Advisor.  I am closing this post because I think before I go any further, I am going to set up a second server from an image of this one and practice.  I am concerned I may break something on our live server....thanks for the input, guys!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A clone is a duplicate copy. Sheep have been cloned and maybe someday even people will be cloned, but disk cloning (performed by the hard drive cloning software) is a vital tool used to manage and protect data. Let’s look at what hard drive cloning …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question