Solved

Query not working on some tables

Posted on 2016-07-27
7
30 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now