Solved

SQL query time

Posted on 2011-09-13
18
302 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrate data to new Mac OS X server 3 55
Uninterruptable power suggestions. 16 72
AWE-based memory on 32-bit servers 1 23
create insert script based on records in a table 4 15
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
What do we know about Legacy Video Conferencing? - Full IT support needed! - Complicated systems at outrageous prices! - Intense training required! Highfive believes we need to embrace a new alternative.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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