Get Performance Tips Directly From SQL Server

Question

Post reply

Get Performance Tips Directly From SQL Server

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

ianstirk Ten Centuries Points: 1310 More actions · Answer 1

Hi Gregory,

Thanks for the updated routine. The derivation of the database from the plan attributes is very useful!

Thanks

Ian

Rudy Panigas SSChampion Points: 10702 More actions · Answer 2

Excellent piece of work! Great job on the article too.

Rudy

pmohan SSC Eights! Points: 941 More actions · Answer 3

I used this and found a proc which is doing a table scan. I put index and then reran this proc again. It is still saying as table scan. But when I captured the actual execution plan by running the proc it is showing an index seek.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

I'm curious why you chose to use an expensive wildcard string pattern match instead of using XQuery with the XML Namespace to query this information? An example of how you can parse the XML Doc using the DOM can be found on the following blog post:

Digging into the SQL Plan Cache: Finding Missing Indexes

ianstirk Ten Centuries Points: 1310 More actions · Answer 5

Hi Jonathan

Thanks for your question.

In a word... “simplicity!”

I wanted the code to be simple and understandable to a wide audience. Not everyone will be familiar with using XML and XQUERY, also the code tends to be bulky and less easy to maintain/extend (compare the code in the link you’ve supplied with the code in this article).

It was a question I asked myself when I was considering the article 😉

It could be argued if you’re interested in performance the XQUERY should be done inside the CLR...

Thanks

Ian

SAinCA SSCrazy Points: 2228 More actions · Answer 6

Thanks for this helpful utility.

Just a heads-up: on our 8-way 64-bit cluster that's rarely rebooted and the cache is rarely flushed, it takes about 4 mins to run this...

Worth the wait, though 😉

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

Great article, very useful information.

Over time it has become clear to me that there is gold in them thar system tables. I wonder if anyone could point me to a reference specifically about the system databases, what is contained and where, and how best to mine that gold.

Thanks.

mgigani-696113 SSC Veteran Points: 227 More actions · Answer 8

I realize that this is an older post but I discovered it just now when it came in the recent mailing.

My problem with this.

getting error on the following line:

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

I have my compatibility set to 90.

Any ideas. I tried this in both 2005 and 2008

Half Bubble SSCommitted Points: 1948 More actions · Answer 9

Curious...

It's telling me it's missing an index: Table InventoryGrouping, Column ClassValuePK, but I already have an index:

CREATE NONCLUSTERED INDEX [IXClassValuePK] ON [dbo].[InventoryGrouping]

(

[ClassValuePK] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

What am I not understanding here?

[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]

ianstirk Ten Centuries Points: 1310 More actions · Answer 10

Hi,

What is the error you're getting?

Do you have access to the DMVs? You need VIEW SERVER STATE permission.

Thanks

Ian

ianstirk Ten Centuries Points: 1310 More actions · Answer 11

Hi,

What index does it say is missing? You should be able to see it's definition in the cached plan.

Thanks

Ian

ianstirk Ten Centuries Points: 1310 More actions · Answer 12

Hi dbowlin,

Have a look at http://www.manning.com/stirk I'm working on a book containing about 100 sample scripts similar to the one in the article. You can download the first chapter (with some nice samples) for free.

Thanks

Ian

Half Bubble SSCommitted Points: 1948 More actions · Answer 13

Um, I don't know. Can't seem to get the same result from the stored procedure again.

[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Nice article Ian. Thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 15

Daniel Bowlin

SSC-Dedicated

Points: 34566

April 3, 2010 at 5:23 pm

#1145405

Thanks Ian. I will definitely check it out.