Solved

SQL Server very slow running queries

Posted on 2015-01-29
9
268 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 49

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 49

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database level memory cache..? 8 37
tempdb log keep growing 7 43
How can I get the entire database script? 7 24
Mysql how to execute a commands file 5 30
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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