Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server very slow running queries

Posted on 2015-01-29
9
Medium Priority
?
341 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 52

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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 70

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 70

Expert Comment

by:Scott Pletcher
ID: 40578004
Do the clustered indexes match the way the tables are joined?
0
 
LVL 52

Expert Comment

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

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

971 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