Lamprey13 (9/9/2011)

---

Sorry I mis-read that as a table variable issue not a tabled valued function.

Can post the code? We might be able to help with some suggestions. If it's...

Sorry, I should clarify: it is a table valued function that uses table variables.

Temp tables do speed things up when I run the individual queries that make up the function. However, table valued functions can't use temp tables.

FredFlintst0ne (3/10/2010)

---

Here's a puzzling table variable speed behavior that I can't explain.

I have a user-defined table function that returns a small resultset (~200 rows).

The user-defined table function employs...

Having the same problem.

Apparently there is a hotfix for SQL 2008:

http://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

Seems to be a known issue. You can try switching the Compatibility level back to 2005 to confirm

The goal here is to identify procs that may need a rewrite. I'm trying to think of a way to create benchmarks for procs using a comparison to other procs...

That makes sense now, I switched to dm_exec_sql_text and I guess our DB is still on 2005. I'll look into sys.dm_exec_procedure_stats as soon as we update.

Thanks for your help!

I think its an aggregate per plan not per procedure. In any case, I get duplicate entries when I don't use aggregates.

I'd Like to use dm_exec_sql_text but I don't have...

How could I do this better? Are there any useful criteria I've left out? This seems to revolve around disk usage, can you see a way to add cpu benchmarks?

allmhuran (6/13/2011)

---

I just wrote a set of procedures to assist with backup/copy/restore. They're built to work with Ola Hallengren's backup and maintenance procedures, taking care of copying the backups and...

This is a start, you could develop this further:

SELECT

last_execution_time

, name

, text

FROM

sys.dm_exec_query_stats AS DMExQryStats

CROSS APPLY fn_get_sql(DMExQryStats.sql_handle)

LEFT...

This one is similar but not parameterized for different objects. It creates a separate .sql file for each object on your servers's C drive in the directory SP_BAK (which you...

This proc creates a mass creation script for all your objects

CREATE PROC ITX_SP_DS_SP_BAK

@P INT,

@FN INT,

@v-2 INT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @t TABLE (Test INT)

declare c cursor local for

(

SELECT

name...