View with index ... ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

View with index ... ?

Author

Topic

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-12 : 20:49:24

Hello,

I created a view from two tables using UNION query, both of the tables holds exactly the same structure (columns & indexes). The query "holds" over one million records and growing by the second (it's a web-log info.). Up to couple of days ago I had no problem(s) working with this view but recently it grew significantly and I keep getting timeout errors when trying to run a query combining this view.

I tried to increase connection and script timeout but it didn't help, I even get the same error message while running the query on the "Microsoft SQL Server Manager" (2005).

My guess would be that including the original indexes (from the tables) in the view might solve this issue but I couldn't find how to accomplish this. Below you may find the view query with a single index, I'm not sure if this is the right solution and if I'm on the right track. Any advise would be highly appreciated.

SELECT ID, Location, Time, Var FROM (
SELECT ID, Location, Time, Var FROM Table_1 WITH (INDEX=ix_Table_1)
UNION ALL
SELECT ID, Location, Time, Var FROM Table_2 WITH (INDEX=ix_Table_2)
) AS t1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 20:57:20

Can you show index structure in tables? Also did you check execution plan?

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 03:34:23

quote:
Originally posted by sodeep: Can you show index structure in tables? Also did you check execution plan?

Sure I can

, each column is by it's own, so Location is ix_Table_1, Time is ix_Table_1_1, Var is ix_Table_1_2 and ID is the key (Table_2 is exactly the same, the only diff. is the table name in the index).

As far as execution plan, I can't get there, keep getting the "timeout ..." error message.

Edit Quote

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:46:11

What is the defragmentation level for the indexes?

E 12°55'05.25"
N 56°04'39.16"

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 03:56:32

quote:
Originally posted by Peso: What is the defragmentation level for the indexes?

I have no idea, how do I check and what is it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:59:22

Open Books Online and read about various DBCC commands.

E 12°55'05.25"
N 56°04'39.16"

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 13:06:06

quote:
Originally posted by Peso: Open Books Online and read about various DBCC commands.

OK, did some reading. Are you referring to 'DBCC INDEXDEFRAG'? should I run it for each and every index\key on both tables and report with the results?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 13:07:24

quote:
Originally posted by Peso

Open Books Online and read about various DBCC commands.

For SQL Server 2005, you shouldn't be using DBCC commands to handle index fragmentation. Instead, we use ALTER INDEX.

medtech26, just use my script as I've done the hard work already:
http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 14:04:57

DBCC SHOWCONTIG, with the INDEX option to see the defragmentaion level of index.
I didn't say you should rebuild or reorganize index, I just wanted to know the defragmentation level.

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 14:06:24

Don't use DBCC SHOWCONTIG for SQL Server 2005, use the DMV as seen in my blog post. Try to avoid deprecated commands as they will disappear in future versions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 14:23:31

quote:
Originally posted by tkizer: ... use the DMV as seen in my blog post.

Hi Tara,

Can you be more specific about the "DMV"?

couldn't find it in the linked blog post (prob. because I have no idea what to look for

).

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 14:27:20

From my blog:

quote:

It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels.

Just run this query though and post the results:


SELECT 
    [object_id] AS ObjectId,
    index_id AS IndexId, 
    avg_fragmentation_in_percent AS FragPercent, 
    record_count AS RecordCount, 
    partition_number AS PartitionNumber,
    index_type_desc AS IndexType,
    alloc_unit_type_desc AS AllocUnitType
FROM sys.dm_db_index_physical_stats (DB_ID(dbNameGoesHere), NULL, NULL, NULL, 'SAMPLED')
WHERE index_id > 0
ORDER BY FragPercent DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 14:54:41

Should I post the complete results list (315 records) or only related results to this issue? (if related results is your answer, how do I link this to the actual indexes\keys?)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 14:59:24

Let's run this instead:


SELECT 
    OBJECT_NAME([object_id]) AS ObjectName,
    OBJECT_NAME(index_id) AS IndexName, 
    avg_fragmentation_in_percent AS FragPercent, 
    record_count AS RecordCount, 
    partition_number AS PartitionNumber,
    index_type_desc AS IndexType,
    alloc_unit_type_desc AS AllocUnitType
FROM sys.dm_db_index_physical_stats (DB_ID(dbNameGoesHere), NULL, NULL, NULL, 'SAMPLED')
WHERE index_id > 0 AND record_count > 1000
ORDER BY FragPercent DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 15:24:46

Have no idea if this is good or bad

but here are the results:

Table_1    sysrowsetcolumns    95    7180    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_1    NULL    90    7180    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_1    NULL    68.75    7180    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_2    NULL    46.9915600326708    1020599    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_2    sysallocunits    14.6684233640755    1020599    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_1    NULL    6.47773279352227    7180    1    CLUSTERED INDEX    IN_ROW_DATA
Table_2    sysrowsets    1.3727560718057    1020599    1    NONCLUSTERED INDEX    IN_ROW_DATA
Table_2    NULL    0.395854922279793    995500    1    CLUSTERED INDEX    IN_ROW_DATA

Table_1 is the small one with no more then 100K records, Table_2 is the large one with over 1M records and growing.

Tara, if I may an off topic question: on your query you submitted the object id with brackets and the index id without, what are the differences between the two objects?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 15:30:47

I put square brackets around all reserved words. MS decided to name one of their columns using a reserved word as OBJECT_ID is a function that gets you the id info when passing the object name: OBJECT_ID('Table_1').

You need to defragment your table. Use my script posted earlier.

Oh and OBJECT_NAME(index_id) AS IndexName is wrong, we can't get the index name that way. You can just use index_id AS IndexId like in my first query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 16:00:43

Should I go with 'REBUILD' or 'REORGANIZE'? Is this process going to harm performance while running? How bad is my situation as is?

I think this issue took me to the next level

! I find out that SQL is like the space, no matter what you knew there's always more to explore ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:18:35

If you have Enterprise edition, it isn't too harmful but depends on your indexes if they can be rebuilt online or not.

If you don't have Ent edition, then it can't be done online, so you should schedule it run in the off hours.

I always use REBUILD, but I've got Ent edition. REORGANIZE is less intrusive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 16:52:43

Just find out that I'm running on Workgroup edition and according to hosting company "there's no difference between Workgroup and Enterprise editions except number of connections simultaneously ...". However, based on my intuition I'm guessing off hours it is.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:55:16

Your hosting company is wrong:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Online indexing isn't available in workgroup edition, so yes off hours would be best. You might consider REORGANIZE for future runs if REBUILD is too intrusive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 17:01:34

Hope everything goes smooth from here ...

Thank you very much for you help Tara & sodeep

Tack så mycket för hjälpen Peso

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources