Solved

SQL Server very slow running queries

Posted on 2015-01-29
9
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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 40

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 69

Expert Comment

by:Scott Pletcher
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
 

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:Scott Pletcher
ID: 40578004
Do the clustered indexes match the way the tables are joined?
0
 
LVL 50

Expert Comment

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

710 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