Indexes

I would like to index my SQL db to make things run faster.  I configured it to index under maintenance plans.  Does that take care of explicitly indexing the db
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I configured it to index under maintenance plans.
Define for us exactly what you did here.   Indexing is not one of my stronger suits, but afaik tables and sometimes views are indexed, databases are not.
0
Daniel WilsonCommented:
The maintenance plan offers to rebuild the indexes you have defined.  This is good maintenance ... but does not define the indexes in the first place.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
I suggest install and use SQL own Performance Dashboard reports (I run it on PROD systems with absolutely no issues and much trust) that has a Missing Index report:

http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
http://blogs.msdn.com/b/arvindsh/archive/2010/06/25/performance-dashboard-reports-in-sql-server-2008.aspx

Or http://technet.microsoft.com/en-us/library/ms345417(v=sql.105).aspx
Or use Brent Ozar's BlitzIndex stored proc: http://www.brentozar.com/first-aid/downloads/end-user-license-agreement/downloads/

OR you can run a query like below to find missing indexes in a SQL database:

--USE THIS IN SQL2005
select --top 10000
      object_name(d.object_id) tablename, gs.unique_compiles, gs.user_seeks, gs.avg_user_impact,
       d.equality_columns, d.inequality_columns, d.included_columns, d.statement as fully_qualified_object--, gs.*
from  sys.dm_db_missing_index_groups g
       join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
       join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle
where  d.database_id =  d.database_id and d.object_id =  d.object_id
            and gs.avg_user_impact > 90
order by gs.avg_user_impact desc, gs.unique_compiles desc, gs.user_seeks desc
order by gs.avg_user_impact desc


--then locate the appropriate CREATE INDEX statement from query below:
SELECT
  mig.index_group_handle, mid.index_handle,
  CONVERT (decimal (28,1),
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig with (nolock)
INNER JOIN sys.dm_db_missing_index_group_stats migs with (nolock) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid with (nolock) ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
and mid.statement like '%Schedule%'
--ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC



/*******************************
      Other useful code below
*******************************/


--top ten missing indexes from SQL Books on Line
SELECT TOP 100 *
FROM sys.dm_db_missing_index_group_stats with (nolock)
where avg_user_impact > 90
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
GO
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulfreelancerCommented:
as an alternate to Brent Ozar's BlitzIndex, this suite of utilities is also highly regarded:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren
http://ola.hallengren.com/
and a recommended video

I recommend the video if considering this suite as Ola is presenting and answering - I found it very informative.

also refer to
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28163719.html#a39265474
0
al4629740Author Commented:
So if I define index the speed of my queries should go radically up?
0
al4629740Author Commented:
I have SQL 2012 btw
0
al4629740Author Commented:
Is this the same thing if I produce a columnstore index
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
al4629740 - Just so you know, you're asking a lot of beginner questions, and not that there's anything wrong with that, but when put them together it's larger than the scope of a typical EE question.  

You might want to consider breaking these out into separate questions.

Also, you can probably google search a lot of this stuff, so this question doesn't start resembling an endless meander through everything about indexes.

Hope you understand.
Jim
0
al4629740Author Commented:
Ok thanks for the advice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.