Solved

SQL query time

Posted on 2011-09-13
18
301 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 125 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 125 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
 

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:sqlservr
sqlservr earned 125 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 125 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now