•  
  • SQL party (5)

AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday

by Muthukkumaran kaliyamoorthy Published on: January 11, 2017
Comments: No Comments
Tags:
Categories:AlwaysON, SQL party

This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar.

 

 

Here is my bug report:

https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active.

It happen one of my production database, when I tried to remove the database from alwaysON and other important databases went recovery pending and inaccessible. I have no idea what happened to all other databases, then I came to know it is a bug.

 

The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state, but the lower database IDs are good and synchronous state only.

http://www.sqlserverblogforum.com/2016/08/alwayson-database-not-synchronized-and-recovery-pending/

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Merge join Vs Hash join Vs Nested loop join

 

This month’s TSQL Tuesday party is being hosted by Stuart R Ainsworth (Blog| Twitter). I am very glad to write my first blog post as t-SQL Tuesday post on my newly designed website.

SQL server has three types of internal joins. I know most of folks never heard this join type because it’s not logical join and it’s not often used in their codes.

Then, when it will be used?

Well the answer is “it depends”.

This means it depends upon the record sets and indexes. The query optimizer will be smart and always try to pick up the most optimal physical joins. As we know SQL optimizer creates a plan cost based and depends upon the query cost it will choose the best join.

How the query optimizer will choose the join type internally?

Well, there is some algorithm has written internally for the query optimizer to choose the join type.

Let’s go for some practical examples and will finally summarize it.
First I will give some basic idea how the join will work and when/How the optimizer will decide to use anyone of the internal join (Physical join).

• It depends upon the table size
• It depends upon the index on the join column
• It depends upon the Sorted order on the join column

Test:

The test has done by following configuration.

RAM: 4 GB
Server : SQL server 2008 (RTM)

 

create table tableA (id int identity ,name varchar(50))
declare @i int
set @i=0
while (@i<100)
begin
insert into tableA (name)
select name from master.dbo.spt_values
set @i=@i+1
end
--select COUNT(*) from dbo.tableA --250600
go
create table tableB (id int identity ,name varchar(50))
declare @i int
set @i=0
while (@i<100)
begin
insert into tableB (name)
select name from master.dbo.spt_values
set @i=@i+1
end
-- select COUNT(*) from dbo.tableB --250600
select * from dbo.tableA A join tableB B
on (a.id=b.id)

 

Test1: Without Index

Let’s create a clustered index

create unique clustered index cx_tableA on tableA (id)
create unique clustered index cx_tableB on tableB (id)

Test1: With Index

If either of the table has indexed then it goes hash join. I haven’t shown this picture here. You can drop either of the table indexes and test it.

Test2: Without Index

Let’s create a medium table

 

create table tableC (id int identity,name varchar(50))
insert into tableC (name)
select name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableC --2506
create table tableD (id int identity,name varchar(50))
insert into tableD (name)
select name from master.dbo.spt_values
select * from dbo.tableC C join tableD D
on (C.id=D.id)
-- select COUNT(*) from dbo.tableD --2506

 

Test2: With Index

Let’s create a clustered index

create unique clustered index cx_tableC on tableC (id)
create unique clustered index cx_tableD on tableD (id)

If either of the table has indexed then it goes merge join. I haven’t shown this picture here. You can drop either of the table indexes and test it.

Test3: Without Index

 

create table tableE (id int identity,name varchar(50))
insert into tableE (name)
select top 10 name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableE --10
create table tableF (id int identity,name varchar(50))
insert into tableF (name)
select top 10 name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableF --10

 

Let’s create a clustered index

 

create unique clustered index cx_tableE on tableE (id)
create unique clustered index cx_tableF on tableF (id)

Test3: With Index

If either of the table has indexed then it goes Nested loop join. I haven’t shown this picture here. You can drop either of the table indexes and test it.

You can also join tables vice versa like big table Vs Medium table Vs small table

select * from dbo.tableA A join tableC C
on (a.id=C.id)

select * from dbo.tableA A join tableE E
on (a.id=E.id)

select * from dbo.tableC C join tableE E
on (C.id=E.id)

 

 

In this case if all the table has indexed then it goes Nested loop join. If they don’t then hash join. If either of the table has indexed then it goes Nested loop join. I haven’t shown this picture here.

Still you can force optimizer to use any one of the internal joins, but it’s not good practice. The query optimizer is smart it will dynamically choose the best one.

Here just I used the merge hint so the optimizer goes to merge join instead of a hash join (Test1 without an index)

 

select * from dbo.tableA A join tableB B
on (A.id=B.id)option (merge join)

select * from dbo.tableA A inner merge join tableB B
on (A.id=B.id)

 

 

 

Table 1: Test uses a unique clustered index

From the table diagram:

  • If both the tables have NO index then the query optimizer will choose “Hash joins” internally.
  • If both the tables have indexes then the query optimizer will choose “Merge (For big tables) /Nested loop (For small tables)” internally.
  • If either of the tables have indexes then the query optimizer will choose “Merge (For medium tables) /Hash (For big tables) /Nested loop (For small & big Vs small tables)” internally.

Table 1: Test using clustered index

(create clustered index cx_tableA on tableA (id))
Table size With index (Both) Without Index(Both) Either of table has index
Big (Both) HASH HASH HASH
Medium (Both) HASH HASH HASH
Small (Both) NESTED LOOP NESTED LOOP HASH
Big Vs Small(medium) HASH HASH HASH

 

 

From the table diagram:

This test has done using without a unique clustered index. See if the index created without unique keyword then there is no guarantee SQL will not know its unique (data) so it will create 4-byte integer GUID unique-identifier by default.

Look the diagram there is no MERGE join if the clustered index created without unique.

Thanks @Dave for your email 🙂 . Now the second diagram added.

 

Conclusion:

Merge Join

Merge join is possible for the tables have an index on the join column. The index either clustered or covering non-clustered index. It’s best join for this circumstance. Why it’s best join? Because it needs an index for both the tables. So it’s already sorted and easily match and return the data.

Hash Join

Hash joins is possible for tables with no index (or) either of the big tables has indexed. It’s best join for this circumstance. Why it’s best join? Because it’s worked great for big tables with no index and run the query parallel (more than one processor) and give the best performance. Most of folk says its heavy lifter join.

Nested loop Join

Nested loop join is possible for small tables with index (or) either of the big tables have indexed. It’s best join for this circumstance. Why it’s best join? Because it works great for small tables like, compares each row from one table to each row from the other table ‘looping’.

For more: Read Gail Shaw’s blog. It has Craig Freedman’s link too. Why I gave Gail’s link instead of directly give Craig‘s link. It’s worth reading and most of my readers are from India and they have to read all her posts.

I hope now you can understand how the query optimizer will choose the most optimal join types.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

T-SQL best practice SQL server

by Muthukkumaran kaliyamoorthy Published on: July 12, 2011
Comments: 13 Comments
Tags: ,
Categories:Performance, SQL party

SQL server T-SQL best practice

This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Blog | Twitter).

He is working with Microsoft and see here his online activities MSDN. The topic this month is T-SQL best practice. It’s the both DBA and developers Topic/Job.

Transact SQL is the best tool to improve the SQL server overall performance. You write your code efficiently then you no need to spend lots of money to buy extra resources like hardware, RAM etc… So the coders/developers have to know the best practice.

He or She does a good job (writing correct codes) then, There is no work for the DBAs. There is lots of T-SQL best practice available on the Internet. Here I’m going to share mine. The first 15 bullet points are personally I have faced and tuned in my environment. I will have planned to write each post for all 15 points with examples.

T-SQL best practice

DOs and Don’Ts

  • Check/Validate your conditions out of the loop (While/If) statement.
  • Specify exact data type size

Ask the customer and specify the correct data type size what they really need.  Otherwise you will end up with a problem when your data grow larger.

E.X For inserting a 4 digit character don’t create CHAR (400) just create CHAR (4)

  • Always write a query using seek-able operator in your WHERE clause.

http://msdn.microsoft.com/en-us/library/ms172984.aspx

  • Try to avoid Functions and date time functions when you write Quires i.e. Sproc .
  • Avoid an index hint

Use the index hint if you really know it’ll improve the performance otherwise avoid it.

SELECT * FROM emp  WITH (INDEX (ix_n)) WHERE n ='server'
  • Don’t create too many indexes.

Create the indexes it’s really useful otherwise don’t. I have seen a table has 5 non clustered indexes with mostly same column definitions for a particular table/SP and the table size is 4 GB and the index size are nearly 9 GB.

  • Don’t use wildcard characters at the beginning of the word
  • Always write seekable codes.
SELECT ID FROM <table_name> WHERE NAME LIKE '%kumar'

-- use below

SELECT ID FROM <table_name> WHERE NAME LIKE 'muthu%'

 

  • Use “order by” and “distinct” – sorting, really when there is a need.

If you have a doubt on that, you can test run the code with/without sorting, you come to know, how it perform well.

  • Normalize your tables

The best design will give the best performance. Normalize your tables before going to your project. I.e. my second point (Specify correct data type size etc…)

  • Try to write set based queries minimize iteration/Cursors as much as you can.
  • Use joins instead of sub query

Use the joins instead of sub query it’ll give better performance.

 

SELECT * FROM TBL WHERE N IN (SELECT N FROM TBL1)

-- use below

SELECT A. * FROM TBL A JOIN TBL1 B
ON (A. N=B. N)

 

  • Use the batch statement if you’re going to delete a huge number of records from the table.

Write a batch statement if you’re going to delete huge records it’ll minimize the log file size. Most importantly inform to the DBA team before going to delete otherwise we will get a call from a customer the DB was down 🙂

  • Use ANSI-Standard Join clauses instead of the old style joins.

 

SELECT e.no,ed.name
FROM employee e, employee_details ed,
WHERE ed.name ='muthu'

-- use below

SELECT e.no,ed.name
FROM employee e INNER JOIN employee_details ed
ON emp.no=ed.no
WHERE ed.name ='muthu'

 

  • Test your data and the indexes after migrating one server to another server especially 2000  to 2005/2008.
  • Create a filtered index if you know the query often fetch the unchanged data from a big table.

A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance too.

 

CREATE NONCLUSTERED INDEX I_fi_test
ON dbo.test(ID,joinDate,Relievingdate)
WHERE joinDate > '2001/01/01';
  • Use NO LOCK hint if you have no problem with dirty reads.

See my friend’s article http://sqlvibes.blogspot.in/2011/07/beware-of-nolock.html

  • Create a clustered index (Primary key) for all mostly used tables

Try to create a clustered index for all the tables. I had seen most of my big tables are not defragmented because of the tables are created primary key with NON clustered index. I asked the reason they said we don’t know who has created.

Use SELECT 1 instead of SELECT *

Use SELECT 1 when you’re checking the records are available at your table.

IF EXISTS (SELECT 1 FROM <table_name> WHERE id=100)
  • In general rule retrieve the columns as you want don’t use “SELECT *”
  • Write a required column in the SELECT statement as you want don’t SELECT all the columns.

E.X

SELECT * FROM <table_name>

-- use below

SELECT c1,c2 FROM <table_name>
  • Don’t write your procedure name starting with “SP_”

SQL server searches all the producers one by one starting with “SP_” in the database including system procedure then finally found yours. It causes minor overhead.

  • Use WHERE condition as much as possible
SELECT * FROM emp WHERE Name ='SERVER'
  • Use TRY-Catch for error handling

See my procedure has written using Try-Catch.

  • Try to avoid dynamic SQL

It’ll generate SQL injections if you’re not writing it effectively.

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

  • Always write your codes/SProcs with descriptions and comments.

I hope this best practice will help for the developers/coders. Thanks to Amit has given this great topic.

Edit: Drop me a comment or email if you have / find any t-SQL best practice I will add that too.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

SQL server Disaster Recovery plan

SQL server Disaster Recovery Options

This month’s TSQL Tuesday party is being hosted by Allen Kinsel (Blog | Twitter) and the topic this month is disaster recovery. It’s a DBA Topic/Job.


The disaster recovery is really “It depends”.

What is disaster recovery?

Disaster recovery is called DR. Hmm…

Disaster recovery is the best option for the business to minimize their data loss and downtime. The SQL server has a number of native options. But, as I already told everything is depends upon your recovery time objective RTO and recovery point objective RPO.

That is how much data can you afford to lose? How long can you wait? How much money do you ready to spend?

If you have answers for these questions then, you can choose your best DR plan.

Who knows what will happen tomorrow?

I have seen most of the company don’t have at least simple DR setup, Like keep the backup in safe DR site. The reason is they didn’t face any disaster yet.

Truncating and deleting the ‘critical tables’ also consider a big disaster. So guys keep the backup, backup and backup the database. Before going to set-up your DR plan asks the questions to DR Guru’s via online (Like twitter, SSC and MSDN) and also read the DR Guru Paul Randal’s white papers it will help.

I have recommended to watch the brentozar‘s DR video. Its amazing video and it covers the basics DR plan.

Seriously I have no idea about Geo cluster. Thanks to @sqlsoldier and @SQLpoolboy  for their twitter reply about the Geo cluster method.

For understandable I will classify the disaster recovery (DR) into three types. I am not going in depth just I will cover the basics of high availability HA and disaster recovery DR.

1. Data center disaster

2. Server(Host)/Drive (Except shared drive) disaster

3. Database/Drive disaster

Backup/Restore

Backup restore is common method its simplest (less expensive) and good option for disaster recovery but it’s depends upon your backup strategy and recovery model. The backup restores will work for all the above three DR methods. The DBA job is keeping the data as much as safe and application availability (Zero down time). That is test and keep your database backups safely try to automate the backup test and move them automatically to the DR site.

The backup restore does not good choose for big databases (TB) in case of any disaster, because the restoration takes more time to bring the database online. However you can speed up the backup restoration using the following SQL native features.

Clustering

Clustering is a combination of one or more servers it will automatically allow one physical server to take over the tasks of another physical server that has failed. Its not a real disaster recovery solution because if the shared drive unavailable we cannot bring the database to online.

Clustering is best option it provides a minimum downtime (like 5 minutes) and data loss in case any data center (Geo) or server failure.

  • Clustering needs extra hardware/server and it’s more expensive.

Database mirroring

Database mirroring introduced in 2005 onwards. Database Mirroring maintain an exact copy of a database on a different server. It has automatic fail over option and mainly helps to increase the database availability too.

  • Database mirroring only works FULL recovery model.
  • This needs two instances.
  • Mirror database always in restoring state.

For more details read Robert L. Davis’s Pro SQL Server 2008 Mirroring book.

Log shipping

Log shipping is the process of automating the full database backup and transaction log on a production server and then automatically restores them on to the secondary (standby) server.

  • Log shipping will work either Full or Bulk logged recovery model.
  • You can also configure log shipping in the single SQL instance.
  • The Stand by database can be either restoring or read only (standby).
  • The manual fail over is required to bring the database online.
  • Some data can be lost (15 minutes).

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain same copies of the data.

Note: Database mirroring, logs shipping and replication also treated as DR if it’s located in different data center. We can use both the HA and DR combination to set up the disaster recovery.

Again everything is “It Depends”. Read Paul Randal’s white papers before going to configure the DR.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Meme Monday: I Got 99 SQL Problems And the Disk Ain’t One

by Muthukkumaran kaliyamoorthy Published on: May 4, 2011
Comments: 4 Comments
Tags: , ,
Categories:SQL party

 

Meme Monday: I Got 99 SQL Problems and the Disk Ain’t One
I am a bit late to this party. I have seen this post from Grant’s blog now only. Thanks to Thomas LaRock such a great topic.

Here is mine:

 

1. Too many indexes on a table

Developers didn’t check their codes and an execution plan too. They just go and create the indexes (Duplicate indexes too).

 

2. Improper memory setting

Improper min, max memory setting especially for 64-bit servers.

 

3. Improperly configured transaction log

Whenever I got a new server, I have seen log backups are failing because, there is no current database backup.

BACKUP LOG cannot be performed because there is no current database backup.

 

4. Poor backup strategy

No SLA for the backups (data) from the customer/Manager.

 

5. Company didn’t believe the DBAs

The important (wealth) project was not running fine. The company suddenly called a consultant even they didn’t consider the DBA but, finally the problem solved by the DBA.

 

6. Rebuilding an index run first then shrinks the database

I have seen in most of the servers a rebuild jobs run first then shrink database runs next.

 

7. Linked server issue

Often I got called from teams the linked server was not working fine. It’s return timeout error.

 

8. Poorly written queries using a Non SARG-able

 

9. No Standard operating procedures


 

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Sunday, June 25, 2017