-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kruti's SQL Blog

I have been working in the IT industry from 2008 onwards. My primary work involves administrating SQL servers. Apart from SQL, I like to learn and use new technologies which can assist a DBA for faster response and lead times. Monitoring, performance tuning, high-availability and database designing are my expertise. I also enjoy coding in platforms such as vb.net, C#, PowerShell and T-SQL.

Unix Basic Commands

In similar context to my previous blog I'm providing a list of UNIX basic commands which you may find handy if your new to the UNIX world.

UNIX Help Command
  • man <command>
  • man man –gives details of the man command
UNIX File Operations Commands

Piping operators for I/O redirection
  • >file…

Read more

0 comments, 393 reads

Posted in Kruti's SQL Blog on 11 April 2016

VI Editor Basic Commands

A few months back I came across an instance of using VI Editor. Trust me being a SQL DBA it was quite a task to remember the editing commands. So I made myself a list of commands that came in handy. Off course you may find amples of site providing…

Read more

0 comments, 608 reads

Posted in Kruti's SQL Blog on 9 April 2016

Implementing Full-Text Indexes

In my previous blog we saw the Full-Text Index architecture. 

http://sqlandmorewithkruti.blogspot.in/2016/04/all-about-searching-full-text-indexes_7.html

In this blog we shall see its implementation. Before you proceed you need to ensure that this feature is added to your instance and the full-text services are running.





We shall start by creating a full text catalog. 

Read more

0 comments, 1,726 reads

Posted in Kruti's SQL Blog on 8 April 2016

All about Searching - Full-Text Indexes

SQL server has the capability to store text-based data in the form of char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max). When querying the data stored in such columns the “LIKE” clause would be highly used for pattern matching.
Consider the below table

CREATE TABLE [dbo].

Read more

2 comments, 1,658 reads

Posted in Kruti's SQL Blog on 8 April 2016

All about Searching - Full-Text Indexes

SQL server has the capability to store text-based data in the form of char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max). When querying the data stored in such columns the “LIKE” clause would be highly used for pattern matching.
Consider the below table

CREATE TABLE [dbo].[Books]

Read more

0 comments, 625 reads

Posted in Kruti's SQL Blog on 7 April 2016

Memory Fundamentals for SQL Server - References


Book References

https://books.google.co.in/books/about/Professional_SQL_Server_2012_Internals_a.html?id=-TjHHnP8ivEC

https://books.google.co.in/books/about/Microsoft_SQL_Server_2008_Internals.html?id=NJUpAQAAMAAJ&redir_esc=y


https://books.google.co.in/books/about/Microsoft_SQL_Server_2012_Internals.html?id=wK1CAwAAQBAJ&redir_esc=y


Topic Wise Blogs


32-bit / 64-bit

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

https://msdn.microsoft.com/en-us/library/ms187499(v=sql.105).aspx


/3GB Switch, /PAE Switch, AWE

https://blogs.technet.microsoft.com/beatrice/2008/08/29/3gb-pae-and-awe-taking-away-some-confusion/

http://blogs.technet.com/b/marcelofartura/archive/2006/09/14/3gb-pae-awe-what-basic.aspx

http://blogs.technet.com/b/perfguru/archive/2007/12/05/scenarios-using-3gb-and-pae-switch-and-appropriate-uses-of-these-switches.aspx

https://blogs.msdn.microsoft.com/chadboyd/2007/03/24/pae-and-3gb-and-awe-oh-my/

http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

http://blogs.msdn.com/b/slavao/archive/2005/06/03/424905.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/10/12/tsql-tuesday-11-misconceptions-enable-awe-on-64bit-sql-servers.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

https://msdn.microsoft.com/en-us/library/ms189334(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms175581(v=sql.105).aspx

https://blogs.msdn.microsoft.com/psssql/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set/

SQLOS / Processes/ Threads/ CPU Models

http://blogs.msdn.com/b/sqlmeditation/archive/2012/12/13/tasks-worker-threads-scheduler-threads-explain-it-all-and-dmvs.aspx

https://technet.microsoft.com/en-us/library/ms189267(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms176043(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ee210547(v=sql.105).aspx

https://msdn.microsoft.com/en-in/library/ms178074.aspx

https://mssqlwiki.com/2013/01/10/sql-server-operating-system-sos-series-1/

https://blogs.msdn.microsoft.com/sqljourney/2012/12/16/an-in-depth-look-at-sql-server-memorypart-1/

http://www.practicalsqldba.com/2012/07/sql-server-sql-server-scheduler.html


SQL Memory Components

http://blogs.msdn.com/b/slavao/archive/2005/03/18/398651.aspx

http://blogs.msdn.com/b/slavao/archive/2005/08/30/458036.aspx

http://blogs.msdn.com/b/slavao/archive/2005/02/19/376714.aspx

Read more

0 comments, 383 reads

Posted in Kruti's SQL Blog on 15 March 2016

Memory Fundamentals for SQL Server - Buffer Pool, Mem To Leave

In the Previous blogs we saw about SQL server logical tree and the various memory caches.

http://sqlandmorewithkruti.blogspot.in/2016/02/memory-fundamentals-for-sql-server-sql.html

In this blog we shall see about Buffer Pool and Mem To Leave a little more in detail.























The Buffer Pool

Buffer Pool comprises of a major component for SQL server memory. It…

Read more

1 comments, 935 reads

Posted in Kruti's SQL Blog on 14 March 2016

Memory Fundamentals for SQL Server - SQL Server Memory Components, SQL Server Memory Logical Tree


In the Previous blogs we have seen about virtual memory related concepts.
http://sqlandmorewithkruti.blogspot.in/2016/01/memory-fundamentals-for-sql-server.html


We came across concepts like Address Window Extension, Physical Address Extension.

We also saw the various CPU related architectural concepts and briefly touch based SQLOS.

In this blog we shall see how SQL server memory…

Read more

1 comments, 2,540 reads

Posted in Kruti's SQL Blog on 22 February 2016

Memory Fundamentals for SQL Server - Process-Thread Model, CPU execution model, CPU scheduling, CPU Architechture, SQLOS

Until now we have seen the windows memory addressing concepts.

http://sqlandmorewithkruti.blogspot.in/2016/02/memory-fundamentals-for-sql-server-awe.html

So how exactly are user applications like SQL server assigned to this space. To understand this we need to see how the application is broken in terms of processes and threads.


Process


A process is an instance of…

Read more

1 comments, 1,245 reads

Posted in Kruti's SQL Blog on 9 February 2016

Memory Fundamentals for SQL Server - AWE ( Address Windowing Extensions), /PAE Switch (Physical Address Extension)

In the previous blog we saw the difference between Virtual Address Space between 32-bit and 64-bit systems. We also saw how virtual memory works and how /3GB switch can be used to increase the user access mode limit.

http://sqlandmorewithkruti.blogspot.in/2016/01/memory-fundamentals-for-sql-server.html

In this module we shall see what is AWE and /PAE…

Read more

5 comments, 1,851 reads

Posted in Kruti's SQL Blog on 1 February 2016

Memory Fundamentals for SQL Server - Virtual Memory, /3GB Switch

Just a few days back I was asked a very basic question related to AWE and /3GB switch.I was surprised being a DBA I was unable to explain the concept and relate the terms by proper choice of words. That's when I realized how important it is to have this…

Read more

2 comments, 894 reads

Posted in Kruti's SQL Blog on 28 January 2016

Basic Windows Administration through Powershell

Hi Guys,

In the previous blog I took you through PowerShell basics We know that PowerShell to works with different modules like SQL server, Active Directory, Windows Administration, IIS, etc. In this blog I shall be updating a few scripts I've used. You may modify the same as per your…

Read more

1 comments, 2,379 reads

Posted in Kruti's SQL Blog on 13 January 2016

Getting Started with PowerShell

Windows PowerShell is all about exploring .NET framework capability through the command line.Announced in 2006 it’s an admins paradise with the number of tasks that can be automated through the command line. The below table gives the release dates for various version of PowerShell.

PowerShell Version
Release Date

Read more

1 comments, 2,127 reads

Posted in Kruti's SQL Blog on 12 January 2016

One Note is the new WordPad

For all those still using Microsoft Word to take memos, prepare templates for email, preparing to do lists or even recording meetings, one note brings an altogether smart and efficient way for you to makes these tasks easier. For book lovers like me who are used to jotting down their ideas…

Read more

2 comments, 647 reads

Posted in Kruti's SQL Blog on 5 January 2016

SQL server Encryption – Transact SQL Functions.

In the previous blogs we came across various encrypt functions like ENCRYPTBYKEY(),ENCRYPTBYASYMKEY(),ENCRYPTBYCERT().
We also saw the corresponding decrypt functions like DECRYPTBYKEY(),DECRYPTBYCERT(),DECRYPTBYASYMKEY(),DECRYPTBYCERT(),
We shall be covering a few more in this blog 
1)  ENCRYPTBYPASSPHRASE() 
This function accepts…

Read more

0 comments, 716 reads

Posted in Kruti's SQL Blog on 15 October 2015

SQL server Encryption – Transparent Data Encryption

In the previous blogs we saw the use of certificates to encapsulate symmetric keys. How it benefits the user by maintaining the password rather than user having to mention it every time a key is opened for use.
In all the above methods we are protecting data at a granular…

Read more

2 comments, 422 reads

Posted in Kruti's SQL Blog on 13 October 2015

SQL server encryption – Certificates

In the previous blogs we saw how to encrypt data using symmetric and asymmetric keys.
We saw how to use functions like ENCRYPTBYKEY, DECRYPTBYKEY & ENCRYPTBYASYMKEY,DECRYPTBYASYMKEY.


Its important to note that while using these functions we need to ensure the password is included correctly.…

Read more

0 comments, 544 reads

Posted in Kruti's SQL Blog on 10 October 2015

SQL server encryption – Asymmetric Keys

In the previous blog we learnt about symmetric key encryption. We also learnt through an example how data is stored in varbinary form after encryption and how the original text can be retrieve using the corresponding decrypt function.


In this blog we will see something similar with the same…

Read more

0 comments, 1,921 reads

Posted in Kruti's SQL Blog on 10 October 2015

SQL server encryption – Symmetric Keys

In the previous blog we learnt about encryption and a brief about the SQL server option provided for data protection through encryption. 


In this blog we will learn with a demo how to use the first option Symmetric Keys for encryption.As stated earlier symmetric keys use the same…

Read more

0 comments, 500 reads

Posted in Kruti's SQL Blog on 10 October 2015

SQL Server Encryption

Encryption is a methodology used to hide confidential information from any illegitimate user is such a way that they do not see the actual contents of the information. Rather they get access to some cipher text. This text can be decrypted to its original form by legitimate users by the…

Read more

1 comments, 1,067 reads

Posted in Kruti's SQL Blog on 10 October 2015

Older posts