• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

SQL Server very slow running queries

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
jknj72
Asked:
jknj72
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>  I am trying to run some queries on tables with a few million rows
What sort of queries. Can you give examples?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Scott PletcherSenior DBACommented:
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
 
jknj72Author Commented:
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
 
Scott PletcherSenior DBACommented:
Do the clustered indexes match the way the tables are joined?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You won't think in moving the MSSQL engine to a new machine?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You should also add non-clustered indexes on all foreign keys:
http://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
0
 
jknj72Author Commented:
Thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now