Hot answers tagged

7

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such ...


7

You can turn on the actual execution plan and then look at the execution plan XML. Or you can use sql sentry's plan explorer tool and see the parameters tab that will list the compiled value and run time value for actual execution plan. If you cannot turn on the actual plan then you can look into plan cache as described below. -- borrowed from Erland ...


4

This query usually perform better: SELECT rn.ID, --... other columns go here rn.OrganisationID FROM ( SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id) FROM #t ) rn WHERE n= 1


3

It will affect overall server performance (so second database will be affected). Shrinking database affects I/O mostly, as it moves pages around data files. But whole operation does put load on CPUs too and it's fully logged, so with database in FULL RECOVERY mode it will put a strain on Your transaction log file too - it will grow up a lot. Shrinking file ...


2

SQL Server has a limitation of 8060 bytes per row. See "Bytes per row" in Maximum Capacity Specifications for SQL Server. Additional information for this line has this phrase: SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length ...


1

Another area to look into is the Default trace which is ON by default. The default trace logs DBCC Events --> Audit DBCC event. So the event gets fired when a DBCC command is ran. Remember that default trace will have 5 max files with 20MB size limit. So if your server is busy, you might miss the event. Alternatively, you can have a WMI alert triggering ...


1

there is a wait type called SQLTRACE_BUFFER_FLUSH -> Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds. this article is very good: Figuring Out When Wait Statistics Were Last Cleared - by Erin Stellato I came out with the following script that seems to be working fine on my servers, including the sql2005 ...


1

Note sure if it will be more efficient select * from ( SELECT PID.ID, PID.OrganisationID , row_number() over (partition by PID.OrganisationID order by PID.id) as rn FROM #t AS PID WHERE PID.OrganisationID <> 0 ) tt where tt.rn = 1



Only top voted, non community-wiki answers of a minimum length are eligible