Search for an Object in SQL Server

Search for an Object in SQL Server

This article helps you in understanding how to Search for an object in SQL Server using T-SQL script. When we need to search for an object inside a database we can do that using sys.objects but when it comes to cross database search that needs a solution. There are different ways to search for an object in SQL Server:

  • T-SQL Script: A native T-SQL script.
  • Object Explorer Search: User required proper permissions to use this
  • Red Gate SQL Search: It’s not always possible installing freeware on enterprise environments
  • SSMS Tools Pack Add-in: An extra tool has to be added to SSMS

Why Instance level object search is required?

There are few cases where we search for objects across the instance.

Continue reading

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , | 4 Comments

Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Introduction:

This post will takes you through the T-SQL Script to monitor SQL Server Memory Usage. In previous blog post we have explained the parameters involved in understanding sql server memory usage. There are total 7 scripts to monitor SQL Server Memory Usage.

  • Buffer Pool Usage
  • System Memory Information
  • SQL Server Process Memory Usage Information
  • Buffer Usage by Database
  • Object Wise Buffer Usage
  • Top 25 Costliest Stored Procedures – Logical Reads
  • Top Performance Counters

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Continue reading

Posted in Interview Q&A, Performance Tuning, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 4 Comments

SQL Server Memory Usage

SQL Server Memory Usage

SQL Server Memory Usage

SQL Server Memory Usage Related Interview Questions and Answers

Introduction:

This post takes you through “how to monitor SQL Server Memory usage”. Most of the clients I worked with had a common question “What is my SQL Server Memory Usage?” yah there are lot of ways to monitor SQL Server Memory Usage. Also if you are a senior database professional whether you might be an administrator, developer or architect you might be asked the question “How do you know SQL Server Memory usage?” One can design the best scalable and an optimized database system when he/she understands the RDBMS architecture. Here we’ll be discussing memory related parameters and how sql server uses memory. Before going through “Monitoring SQL Server Memory Usage” we should understand below things.

  • Memory Types
  • Top Performance Counters
  • Memory Allocation to SQL Server
  • Virtual Address Space
  • Buffer Pool and Memory-To-Leave (MTL)
  • Lock Pages in Memory
  • Instant File Initialization
  • Logical Reads & Physical Reads

Continue reading

Posted in Interview Q&A, Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 2 Comments