How to: Change The Window Title in Windows PowerShell
This post will shows you some tips on how to change the Windows PowerShell title to your desire name. This will be very helpful when you on multitasking, for instance tail log files from multiple sources. Thus, it allows you to name your process window nicely.
Execute the following scripts in your Windows PowerShell console.
$a = (Get-Host).UI.RawUI $a.WindowTitle = "Powershell Title" Get-Content C:\ProcessLogs\logs_06-18-2013_2217.log -Wait
How to: Find The Created Date of The Database
The following T-SQL statement will helps when you wish to find out when your database created. You should check out this, if you’re interested to find out the created date and modified date of each table in your SQL Server database.
SELECT name, crdate FROM master..sysdatabases
How to: Tail Your Log File Using PowerShell
For those who familiar with the “Tail” Unix command, which used to monitor the log inserted to certain log file, may wish to have the similar tool apply in Windows environment. Unfortunately the “Tail” command doesn’t work in MS Command Prompt. In this post, we will teach you how to customize it and create your own “tail” function in MS Command Prompt.
Step 1:
You need to make sure you have Windows PowerShell installed in your machine.
Step 2:
Navigate thru Start > All Programs > Accessories > Windows PowerShell if you’re using Windows 7. If you couldn’t find Windows PowerShell in your machine, you may have to download it from Microsoft website. Click on the link and you will see a blue console show up in front of you.
Step 3:
Type the following command:
Get-Content <your_file_name> -Wait
Enhancement
If you are looking for a script which will automatic allocate the latest file in your log directory and tail the running log file, you are in the right place. You may need to copy the following script to your notepad or code editor and replace the value $dir variable. After that copy the whole thing and paste it in your Windows PowerShell console and press ENTER. That’s it!
$dir = "F:\Report_Logs\" $latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1 $fullpath = $dir + $latest Get-Content $fullpath -Wait
What is: The Difference Between a Clustered and a Non-Clustered Index?
A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A non-clustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a non-clustered index as a book’s index.
Reference(s)
TechRepublic – SQL Server Database Developer Interview Questions and Answers
How to: Get I/O Utilization by Database
This cool scripts obtain from Glenn Berry’s SQL Server Performance Scripts library to help you to find out the I/O utilization for each databases in your SQL Server instance. If you are looking for a script to shows you which database are the busiest one, like I do, in our database instance, this will the right script you need to grab and put in your toolbox.
-- Get I/O utilization by database (IO Usage By Database) -- Helps determine which database is using the most I/O resources on the instance -- Scripts adopted from SQL Server 2008 R2 Diagnostic Information Queries (February 2013).sql SELECT DB_NAME(database_id) AS [Database Name], CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS] GROUP BY database_id;
What is SQL Server CLR Integration and it Benefits?
CLR stands for “Common Language Runtime”. It is the heart of Microsoft .NET Framework and provides the execution environment for all .NET Framework code.
Benefits of this integration:
- A better programming model
- Improvesafety and security
- Ability to define data type and aggregate functions
- Streamlined development through a standard environment
- Potentiall for improvement performance and scalability
What is Data Collector?
Data Collector (DC) consists of 3 data sources, there are TSQL Queries, SQL Trace, and Performance Monitor Objects/ Monitor.
The 2 key databases that drive the data collector sub-system are the MSDB and MDW.
MSDB contains all or most of the data collector’s metadata and configuration. For MDW, it contains (mostly) the actual data collected into “snapshot” tables.
How to: Sort SQL Results in Your Own Preferred Order
SELECT * FROM [SQL_ServerList] ORDER BY (CASE ServerType WHEN 'PROD' THEN 1 WHEN 'QA' THEN 2 WHEN 'DEV' THEN 3 END), [Instance Name], [SQLServer Name]
How to: Conver the Column to Sentence Case Format
Convert the first character to uppercase and the rest to lowercase.
select DESCRIPTION = -- Assuming that the length of DESCRIPTION is varchar(500) convert(varchar(500), upper(substring(DESCRIPTION,1,1))+ lower(substring(DESCRIPTION,2,499))) from MyTable
How to: Identify Blocking Queries
The following scripts will generate a list of blocking SPIDs in the SQL Server instance
SELECT
convert(nvarchar(100), SERVERPROPERTY('Servername')) AS [Server Name],
db.name AS [Database Name],
tl.request_session_id AS [SPID],
wt.blocking_session_id AS [Blk By],
OBJECT_NAME(p.OBJECT_ID) AS [Blk Object Name],
tl.resource_type AS [Resource Type],
h1.TEXT AS [Requesting Text],
h2.TEXT AS [Blocking Text],
tl.request_mode AS [Lock Mode]
FROM
sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
Reference:
Identify Blocking Queries
http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/
Find Blocking Processes
http://www.techrepublic.com/blog/datacenter/find-blocking-processes-using-recursion-in-sql-server-2005/275