Solved

SQL Server very slow running queries

Posted on 2015-01-29
9
257 Views
Last Modified: 2015-01-30
Ok I just started a job doing Business Intelligence work and we are running SQL Server 2008 R2. I am trying to run some queries on tables with a few million rows and its not finishing so I end up cancelling the query. I have a feeling that I need to get some advice on a few things. I noticed the SQL Server doesnt have any SPs installed. I also see that all the database data and log files are both on the same drive. This server is being used for Business Intelligence apps so there is a job that runs a few times a day that pulls data(millions of rows) via SSIS into the SQL Server from an ODBC connection to a mainframe. It takes forever to run and want to figure out the problem.  I tried to check the Trans log and ran DBCC LOG(database, 1)  and it returned 3.3 millionj rows. I have a feeling I need to do some maintenance maybe a SHRINKFILE but want to know what others think...
I am getting a new server built now and want opinions on the setup. I have always put the data and log files on seperate drives and wanna ask if I should do that? We have a virtual netowrk setup and I asked about the RAID config and was told it wouldnt matter because of this VM setup? Is this true? Could this be causing our issues? PLEASE HELP!!
0
Comment
Question by:jknj72
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40577616
>  I am trying to run some queries on tables with a few million rows
What sort of queries. Can you give examples?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40577626
Is the SSIS installed in the same server that SQL Server engine?
If so, the first thing you need to do is to move the SQL Server to a new server. SSIS is a memory eater and will eat memory needed by the SQL Server engine.
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40577658
So if it's a VM it depends on what the underlying hardware is.  If the underlying hardware is raided then having one drive versus multiple shouldn't make a difference.

The only time that it would actually help is if you had VMs talking to two different set of spindles.  (EG: Mount C drive from one physical disk and D drive from another set).  

Some good info on the physical layout of things here:
http://community.spiceworks.com/topic/331472-best-practices-to-configure-raid-drives-on-virtual-server-for-sql-2008


With regards to the actual performance . . . the major suspect especially dealing with that many rows is indexing.

Essentially you want to make sure that you're tables have a primary key.  Ideally that's the best way to look up information.

I have a table with a million records, looking up by the primary key is still less than a second.

The next thing you want to do is create your non clustered indexes.   This will help your query along.  More on that here:
https://technet.microsoft.com/en-us/library/ms189607%28v=sql.105%29.aspx


essentially you want to create your indexes in such a way that makes sense.  You also need to order them individually.  (EG:  FIrst By LastName, then by FirstName)

If that was the case, could do something like

select * from table where lastname = 'Jones'
or
select * from table where lastname = 'jones' and firstname  ='Tom'

but you couldn't do:
select * from table where firstname = 'Tom'  -- results in full table scan unless a second index is created.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40577771
For best performance, you need to get the best clustered index on each table, esp. large tables.  [A primary key per se is not required.]

Determining the best clustering column(s) can naturally be challenging at times.  You need to use your own knowledge of the table and it uses, combined with SQL's index usage stats and missing index stats, to figure that out.  Then put those indexes in place.  After getting the best clustered index, you can decide whether any nonclustered index(es) are needed, and what they should contain.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jknj72
ID: 40577914
Well the tables in the DB are built in a snowflake schema and have all these foreign keys on it with a primary as well. The rest of the columns are essentially measures and numeric values. What would you suggest as far as a data warehouse snowflake table structure for indexing?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40578004
Do the clustered indexes match the way the tables are joined?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40579229
You won't think in moving the MSSQL engine to a new machine?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40579967
You should also add non-clustered indexes on all foreign keys:
http://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
0
 

Author Closing Comment

by:jknj72
ID: 40580334
Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 90
Retention Policy for Backups 1 13
Log Backup 2 13
Haw to apply join on 2 tables with this scenario 4 10
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

16 Experts available now in Live!

Get 1:1 Help Now