Archive for the ‘SQL’ Category

How SQL Server Executes a Query   1 comment

For every developer whether he is fresher or experience should know how SQL internally execute the query in order to dig if there is challenge in future related to performance.

  • whenever there is new request come for the SQL server in the form of SQL statement then, request created and pass to SQL server using network protocol, this task is queued in Task Queue and wait for the worker to take it further.
  • Idle worker take the task and pass to execution unit, task execution unit perform the syntax checking using parsing and compilation process and once that is completed it look for the best execution plan using optimization process.
  • Once execution plan is prepared, Operators access the data through buffer pool.
  • Buffer pool is responsible for extracting data from file system (database).
  • Data returns to the requested client and not wait for the completion, the worker process process not released till complete data extracted and return to the calling program.
  • Once task completed, worker returns to the idle state.

 

Advertisements

Posted March 18, 2018 by Izharuddin Shaikh in SQL

Tagged with

Best way to build LDF   Leave a comment

Generally, when the log file size become very huge and even after shrinking the same still we face file size issue in that case we take an action to regenerate the new LDF file for our database. There are different ways to achieve the same, but the best way to achieve it is as follow:

  1. use sp_helpdb yourdbname and note down the path where .mdf and .ldf files are stored.
  2. execute the sp_detach_dp yourdbname command to detach the database Syntax
    sp_detach_db [ @dbname = ] 'dbname' 
                 [ , [ @skipchecks = ] 'skipchecks' ]

    example:

    EXEC sp_detach_db 'youdbname', 'false'
  3. Move the original database files to some other location or rename them.
  4. Create database with same name using CREATE DATABASE yourdbname command.
  5. Stop SQL server and replace the .mdf file.
  6. Start SQL server.
  7. Database will be shown in pending recovery mode.
  8. Issue below list of commands to bring the database online

Use Master

Go

Alter Database yourdbname Set Emergency

Go

Alter Database yourdbname Set Single_User

Go

DBCC CheckDB(yourdbname, Repair_Allow_Data_Loss)

Go

Alter Database yourdbname Set Multi_User

Go

Alter Database yourdbname Set Online

Go

 

Note*: You will receive error messages but database will be online.

 

Posted March 13, 2018 by Izharuddin Shaikh in SQL

Tagged with

Terminology in SQL Part 2   Leave a comment

Extent – the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

Uniform extents – owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents – shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Allocation Unit – a set of particular types of pages.

Partition – is a unit of data organization.

Heap – a table without a clustered index.

IAM – Index Allocation Map-the page that keeps track of all the pages allocated to a heap. (Can be more than one)

B+ Trees – B-tree stands for “balanced tree,” and SQL Server uses a special kind called B+ trees (pronounced “b-plus trees”) that are not kept strictly balanced in all ways at all times. Unlike a normal tree, B-trees are always inverted, with their root (a single page) at the top and their leaf level at the bottom.

Root Node – The top node of the B+ tree is called the root node.

SGAM – tracks shared extents.

GAM- tracks an allocation event.

Posted September 9, 2017 by Izharuddin Shaikh in SQL

Tagged with

Terminology in SQL Part 1   Leave a comment

Page – The fundamental unit of data storage in SQL Server

DBCC PAGE – allows you to examine the contents of data and index pages.

DBCC IND – lists of all database pages that make up the selected index or partition.

Data Page – stores data,except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

Log Files – a series of log records.

Extents – a collection of eight physically contiguous pages and are used to efficiently manage the pages.

.mdf – Primary data file.

.ndf – Secondary data file.

Posted September 9, 2017 by Izharuddin Shaikh in SQL, Uncategorized

Tagged with

Find Wait and Blocks In SQL   Leave a comment

This query the sys.dm_os_waiting_tasks DMV to show all waiting tasks currently active or blocked,
revealing the wait type, duration, and resource

SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id

Output:

o

Posted January 1, 2017 by Izharuddin Shaikh in SQL

Tagged with

Sending Email using SQL Server   Leave a comment

Follow the below steps to configure email account and send email using SQL Server

–Enabling Database Mail
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘Database Mail XPs’,1
reconfigure

–Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLProfile’,
@description = ‘Mail Service for SQL Server’ ;

— Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQL_Email_Account’,
@email_address = ‘abc@gmail.com’,
@mailserver_name = ‘smtp.gmail.com’,
@port=587,
@enable_ssl=1,
@username=’abc’,
@password=’password’

— Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLProfile’,
@account_name = ‘SQL_Email_Account’,
@sequence_number =1

— Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLProfile’,
@principal_id = 0,
@is_default = 1 ;

–Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’,
@recipients = ‘abc@gmail.com’,
@body = ‘Database Mail Testing…’,
@subject = ‘Databas Mail from SQL Server’;

–Verifying, check status column
select * from sysmail_allitems

 

Posted March 5, 2016 by Izharuddin Shaikh in SQL

Tagged with

SQL Server Basic – VII   Leave a comment

–Joining two tables without a clause i.e, cross products for two tables

USE Pubs
SELECT * from authors,titleauthor
GO
SELECT * from authors cross join titleauthor
GO

This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table
and 30 records in the second we will get a total of 600 records.
Not putting a where clause is same as a CROSS JOIN.

–Using cross products to generate unique derived fields

USE Pubs
SELECT distinct (stor_id + '-' + au_lname) compfld
from stores cross join authors
order by compfld

‘+’ operators concatenates two fields for composite field generation Primary key of two different tables can be used to create
a composite key in a link table this way.

–Using Inner Joins to find exact matching rows

SELECT au_id,pubdate
from titleauthor inner join titles
ON titleauthor.title_id = titles.title_id

INNER JOIN exactly matches the common key on either sides of the Join

–Conventional Inner Joins

SELECT au_id,pubdate
from titleauthor, titles
WHERE titleauthor.title_id = titles.title_id

–Inner Joins having more than two tables

SELECT   au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]
from       titleauthor
INNER    JOIN titles
ON          titleauthor.title_id = titles.title_id
JOIN      publishers
ON          publishers.pub_id = titles.pub_id
ORDER   BY PUBLISHER

INNER JOIN is the default type of Join. Specifying it is not always needed

–Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],
Convert(char(12),pubdate) [PUBLISHED ON]
from titleauthor
JOIN titles
ON titleauthor.title_id = titles.title_id
JOIN publishers
ON publishers.pub_id = titles.pub_id
WHERE pub_name like 'ALGO%'
AND au_id >= '3%'

Multiple Inner Joins further refined by a Where / And Clause Convert function gives the date in a more friendly format.
Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)
‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

–Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titles a
left join titleauthor b
ON a.title_id = b.title_id
order by a.title,b.au_ord

Left Join brings all the rows from table appearing first in the JOIN clause

–Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titleauthor b 
right join titles a
ON a.title_id = b.title_id
order by a.title,b.au_ord

Right Join brings all the rows from table appearing second in the JOIN clause.

–LeftJoin in two tables having a filter

select                    stor_name,CAST(discount AS VARCHAR) + '%' '%'
from                       discounts
LEFT JOIN              stores
ON                          discounts.stor_id = stores.stor_id
WHERE                   discounts.stor_id IS NOT NULL

The results can be filtered using a trailing Where.
Left join brings all the records from discounts & nulls for unrelated stores records CAST does the same jobs as convert but lacks formatting options Casting needed as % is a character.
Here first ‘%’ becomes part of the data & the second becomes header of the column.

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

%d bloggers like this: