Buffers used by current database objects

 Buffers used by current database objects

This SQL query can take some time to execute, especially on a busy database instance. It's designed to provide insights into the buffer usage of your SQL Server, helping you identify which objects are consuming the most memory.

SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
       OBJECT_NAME(p.[object_id]) AS [Object Name],
       p.index_id,
       CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
       COUNT(*) AS [BufferCount],
       p.[Rows] AS [Row Count],
       p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id
INNER JOIN sys.objects AS o WITH (NOLOCK) ON p.object_id = o.object_id
WHERE b.database_id = CONVERT(INT, DB_ID())
  AND p.[object_id] > 100
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
GROUP BY o.Schema_ID,
         p.[object_id],
         p.index_id,
         p.data_compression_desc,
         p.[Rows]
ORDER BY [BufferCount] DESC
OPTION (RECOMPILE);

Understanding the Query's Output:

• Schema Name: The schema to which the object belongs.
• Object Name: The name of the database object (table, index, etc.).
• index_id: The ID of the index on the object.
• Buffer size(MB): The estimated memory consumed by the object in megabytes within the buffer cache.
• BufferCount: The number of 8KB pages allocated to the object in the buffer cache.
• Row Count: The total number of rows in the object.
• Compression Type: Indicates if the data is compressed and, if so, the type of compression used.

This query can be a valuable tool for performance tuning and memory management in your SQL Server environment. By identifying objects with high buffer consumption, you can investigate potential optimizations like indexing strategies, data compression, or query rewrites.

Leverage Standard RegEx in SSMS for Powerful Searching

SQL Server Management Studio (SSMS) allows you to perform advanced search and replace operations using Regular Expressions (RegEx). These can be particularly helpful for cleaning up code, refactoring queries, or removing unnecessary characters. Below are some useful RegEx examples that you can apply to your SSMS queries.

---

1. Removing Blank Lines

If you’ve imported code from a source like Firefox, you might encounter unwanted blank lines. To remove these, you can search for:

\n @\n

And replace it with:

\n

---

2. Finding Un-commented 'INT' Instances

To find all instances of INT that aren't commented out, use:

^~(:b<--).:bINT>

This RegEx checks for lines that aren't preceded by a comment (--), making it easier to find INT instances in active code.

---

3. Selecting Up to 100 Characters in a Line

To select the first 100 characters of a line (and all characters if fewer than 100), use:

^(.^100)|(.*)

This will capture up to 100 characters at the start of the line and will fall back to matching the entire line if the line is shorter.

---

4. Inserting a String at a Specific Position

To insert a string at a specific column position (e.g., after the first 100 characters), use the following:

Find:

^{.^100}

Then, replace it with:

\1'I've inserted this'

This will insert the specified text after the first 100 characters.

---

5. Deleting Columns After a Specific Position

To delete 10 characters after column 100, use:

^{(.^100)}{.^10}

Replace with:

\1

This RegEx will preserve the first 100 characters while removing the next 10.

---

6. Finding Quoted or Delimited Strings

To find strings enclosed in either quotes or delimiters, use:

("[^"]*")|([[^[]])

This captures quoted strings as well as strings enclosed by square brackets.

---

7. Replacing Quoted Strings with Delimiters

To replace all quoted strings with delimited strings, use the following find and replace pattern:

Find:

("[^"]*")

Replace with:

[\1]

---

8. Removing Inline Comments

If you want to remove inline comments that take up the entire line, use:

^:b<--.>$

Replace with nothing. This RegEx will remove lines that only contain comments.

---

9. Finding Valid Object Names with Delimiters

To find valid object names within delimiters, use:

[{[A-Za-z_#@][A-Za-z0-9#$@_]*}]

And replace with:

\1

This will remove unnecessary delimiters while ensuring the object name is valid.

---

10. Finding 'Tibbling' Prefixes (tbl, vw, fn, usp)

To find object names that begin with prefixes like tbl, vw, fn, or usp, use:

<(tbl|vw|fn|usp)[A-Za-z0-9#$@_]*>

This RegEx helps identify objects using certain naming conventions.

---

11. Removing Tibbling Prefixes

To remove these prefixes (e.g., tbl or usp) from the object names, use:

Find:

<(tbl|vw|fn|usp){[A-Za-z0-9#$@_]*}>

And replace with:

\1

This will keep the object name while removing the prefix.

---

12. Matching Words with 3 or More Characters

If you need to match words that are at least 3 characters long, use:

<:c^3:c*>

This is a workaround for the lack of proper range quantifiers in SSMS.

---

13. Finding Multi-line Comments

To find multi-line comments that start with /* and end with */, use:

/*(:Wh|[^:Wh])*/

This RegEx works around the limitation of stopping at the end of a line.

---

14. Finding Title-Cased Words

To find words that start with a capital letter followed by lowercase letters, use:

<:Lu:Ll[a-zA-Z0-9_$]*>

This helps find properly capitalized words in your code.

---

15. Removing Headers in Code (e.g., SMO Comments)

To remove headers added by SMO (e.g., comments about object names and script dates), use:

Find:

/[*]^6.<[*]^*6/

Replace with nothing.

---

16. Commenting and Uncommenting Lines

• To comment out lines, select the lines and use:

  Find:

  ^

  Replace with:

  -- (or your comment character)

• To uncomment lines, use:

  Find:

  ^:b<--{.}*$

  Replace with:

  \1

---

17. Finding Two Words Separated by Up to Three Words

To find two words (e.g., FROM and AS) separated by up to three words, use:

\bFROM(?:\W+\w+){1,3}?\\W+AS\b

This RegEx allows for more flexibility in matching phrases between two keywords.

---

18. Finding the First Object Referenced by a FROM Clause

To find the first object referenced by a FROM clause, use:

^~(:b<--.){(:Wh|[^:Wh])#}&< (ON|CROSS|RIGHT|INNER|OUTER|LEFT|JOIN|WHERE|ORDER|GROUP|AS)

This matches keywords after FROM, such as INNER JOIN or WHERE.

---

19. Finding Integers or Floating-point Numbers

To find either integers or floating-point numbers, use:

~(. )<([+-]@[0-9]@.[0-9]E[+-][0-9])|([+-]@[0-9].[0-9]*)|([+-]@[0-9]+)

This captures both types of numeric values in your code.

---

Metacharacters Outside Square Brackets

Character	SSMS Equivalent	Meaning
\	\\	General escape character
^	^	Assert start of string or line
$	$	Assert end of string or line
.	.	Match any character except newline
[	[	Start character class
]	]	End character class
`	`	`
(	(	Start sub-pattern
)	)	End sub-pattern