Technical Debt is a programming concept that is also known as code debt. The concept of Technical Debt applies closely with Agile software development, but also applies to database development. There are many tools available to report on technical debt for C#, PHP or other programming languages, but there isn’t much available to manage the SQL Server or T-SQL technical …
Just the same query that I posted months ago, but this time it is wrapped in a stored proc. I use this one often enough that it is easier to just call the stored proc. Download the FindOneTimeUseQueries source code. TSQL example: CREATE PROCEDURE [dbo].[Findonetimeusequeries] AS BEGIN SET nocount ON; SELECT TOP 100 refcounts, usecounts, size_in_bytes /1024 AS sizeinkb, TEXT FROM sys.dm_exec_cached_plans CROSS APPLY sys.Dm_exec_sql_text(plan_handle) WHERE cacheobjtype = ‘Compiled Plan’ AND objtype = ‘Adhoc’ AND usecounts = 1 AND TEXT NOT LIKE ‘FETCH API_CURSOR%’ ORDER BY size_in_bytes DESC END This posting …
There is a newer version of this posting, click here. Part of my regular database maintenance or tuning routine is to look for the queries with cached plans that are taking up the most space, and have only been used once. The query below is a handy query to track down the queries that may be in need of paramaterization. …
The following applies to SQL Server 2008. How big of an allocation does max represent in the case of varchar(max) and varbinary(max)? Up to 2 ^ 31 – 1 bytes or just about 2gb. Can I specify a varchar(9000) or varbinary(12000)? No. If you are going to specify an actual number instead of (max) then your limit is 8000. …
varchar(max) and varbinary(max) Questions and Answers Read more »