Solved

Query not working on some tables

Posted on 2016-07-27
7
31 Views
Last Modified: 2016-07-27
Heyas,

The following query:

SELECT

IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Source:

http://social.technet.microsoft.com/wiki/contents/articles/25473.sql-server-dmv-to-track-page-split.aspx

Doesn't always work the error message I get is:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '('.

Any help is always welcome.

Thank you.
0
Comment
Question by:Zack
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41732266
I cut and paste it and it works in SQL Server 2014.
In what context does it not work?  A different server with a different version of SQL Server?  Did you try to change something or add something to it?
0
 

Author Comment

by:Zack
ID: 41732279
Hi Chris,

In some tables in SQL Server 2008, that the odd thing is for some tables it works fine for others it gives that error message described.

Thank you.
0
 

Author Comment

by:Zack
ID: 41732284
Hi Chris,

Additional info SQL Server 2008 (100) compatibility level.

Thank you.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Assisted Solution

by:Zack
Zack earned 0 total points
ID: 41732287
Hi Chris,

CORRECTION: Additional info SQL Server 2000 (80) compatibility level. On tables that give an error when running the query. I think this is it. Can you confirm.

Thank you.
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 41732289
a little hard to confirm.
 :-)
do you know how many years it has been since I even had a SQL Server 2000 machine to touch, but I bet that is a big problem, the query uses DM_DB_INDEX_OPERATIONAL_STATS which was introduced in 2008 R2, so it is blowing up on the "(" following that.
0
 

Author Closing Comment

by:Zack
ID: 41732292
Good point Chris :), thank you for the assistance.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41732294
You're welcome!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

785 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