Using Perfmon in SQL SERVER

Hi Guys,

In SQL SERVER,every tool is very important to monitor the certain tasks executing on the sql server.In on that track performance monitor also plays very key role in the sense of monitoring the sql server.

performance monitor is the windows level tool to monitor the hardware and software utilization in the system.In perfmon counters are objects which will show the utility of the resources using by certain software nothing but sql server.

I wanna share some of the basic counters in perfmon.

SQL Server Access Methods object: Page Splits/sec

To monitor number of page splits w.r.t SQL Server. If the number of page splits is high, we need to increase the fill factor of our indexes there by providing more room in data pages and subsequently reducing the chances for Page Splits.

SQL Server Buffer Manager Object: Cache Size (pages)

To monitor the amount of physical RAM used by SQL Server's data cache. This number is presented in pages. We should see that no more than 60 %

SQLServer: SQL Statistics: Batch Requests/Sec counter

This counter measures the number of batch requests that SQL Server receives per second. Value greater than 1000 batch requests per second indicate a busy Server, and could lead to CPU bottleneck.

SQLServer: SQL Statistics: SQL Compilations/Sec counters

To monitor compilations on SQL Server requests per second. This counter should not exceed 150.

SQLServer: Databases: Log Flushes/sec

To monitor the number of log flushes per second. This can be measured on a per database level, or for all databases on a SQL Server. If the counter is more, we need to increase RAM allocated for SQL Server.

SQL Server General Statistics Object: User Connections

To monitor the number of user connections, not the number of users, those are currently connected to SQL Server.

Server Locks Object: Number of Deadlocks/sec

To minotor the number of deadlocks per second. Since the monitoring is done on per second basis, we may always see some number for this counter. For average, we need to average this value to seconds.

SQL Server Locks Object: Average Wait Time (ms)

To monitor the average wait time for locks. This counter is very useful if the users experience waits for their transactions to complete.

SQL Server Access Methods Object: Full Scans/sec

To monitor the number of table scans SQL Server instance is performing. This counter is for an entire server, not pertaining to a specific database.

SQL Server Buffer Manager Object: Buffer Cache Hit Ratio

To monitor how often SQL Server access buffer and not the hard disk, to retreive data. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted.

SQLServer:Memory Manager: Total Server Memory (KB)

To monitor how much total RAM the instance is using.

SQLServer:Memory Manager: Target Server Memory (KB)

Specifies how much memory SQL Server would like to have in order to operate efficiently. This is actually based on the number of buffers reserved by SQL Server when it is first started up.

ADVANCED COUNTERS:

There are some SQL Server counters that you can watch to help determine if your SQL Server is experiencing any problems.

* SQL Server Buffer Mgr: Page Life Expectancy: To specify how long data pages are staying in the buffer. If this counter gets below 300 seconds, this is an indication that SQL Server could use more memory in order to boost performance.

* SQL Server Buffer Mgr: Lazy Writes/Sec: This counter tells us how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Ideally, it should be close to zero which indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.

* SQL Server Buffer Mgr: Checkpoint Pages/Sec: Higher Value of this counter indicates that the checkpoint process is running more often than it should, which more server resources. In those cases, we need to increase RAM to reduce how often the checkpoint occurs or decrease the "recovery interval" SQL Server.

* SQL Server Buffer Mgr: Page Life Expectancy: To monitor how long data pages are staying in the buffer. If the value is less than 300 seconds means, an indication that the SQL Server could use more memory to boost overall performance.

CERTIFICATES AND SIGNATURES IN SQL SERVER

Hi Guys,

In General, Real world environment of sql server will completely depend on certificates and signatures for encryption and decryption of confidential information like 'passwords' for the application end users.

I want to share you the basic concept of certificates.

first we need to create the master key for certificates, based on the key encryption going to be held.

---creating the master key

create master key encryption by password = 'admin123'

---Creating the certificate

create certificate [certificatename]

with

subject = 'sample_certificate'

---getting the certificate id

select cert_id('certificatename')

----encrypt the text by using the certificate

select encryptbycert('cert_id',N'sample text')

Hope u guys got the minimum basic idea about cerificates in sql server

LOGINS AND USERS FOR SQL SERVER(security)

Hi Guys,

Today i wanna share an interesting topic called security in SQL SERVER.

At present in the web security plays a very major important role because to secure the confidential information from the threat of hackers as on the same way SQL SERVER also providing the great security feature called logins and users.

In general SQL SERVER having two types of authentication

1. windows level authentication
2. SQL level authentication

In the windows level authentication no need of providing any password to the sql server to connect,directly we can access the sql server by using the windows level credentials.where as in the SQL level authentication we must have to provide the password to get connect to the sql server.In this scenario sql server never use the default credentials user defined credentials are must.

The default LOGIN for sql server is [DBO]

He can access any databases and also having all the privileges to work on sql server.He was allowed to perform some interesting operation called creating the login and specifying the privileges.

In the server level we do have roles for specific privileges to Logins.

• sysadmin:Any type of operation on entire application
• server admin:Any operations on entire server.
• security admin:Provide permissions/access to other logins and users.
• process admin: Deal with spid s , processes and system-level session audits.
• bulk admin: Can make any Bulk operations like copy,import and export and select into
• disk admin: Can perform any disk level operations.
• db creator:can make any operations DDL statements.
• public: Having the limited access to the server like METADATA.

That s all the security for Server level.

We do have one more security concept for database users. Followed on Next blog.

Thanks guys hope u got the basic idea about sql server security.

IDENTITY PROPERTY

HI guys,

hope u guys are doing well. Today i am going to introduce a new concept called IDENTITY PROPERTY.

IDENTITY PROPERTY means that name itself indicating providing the identity sequence of numbers automatically to a table column in the database.

Example for IDENTITY PROPERTY:

Method 1 : giving the identity property for a table.

create table example_tab

(

emp_id int primary key identity(100,1), --100 is the seed and 1 is incremental

emp_name nvarchar(30) not null

)

Thanks for visiting my blog.

Pushing the data of 2 tables into 1 new table

hi Guys,

Basically there are two ways to solve the scenario

1st solution: By using the FULL OUTER JOIN

Example:

insert into table3 select t1.col1,t1,col2,t2.col3 from table1 as t1

full outer join

table2 as t2

on t1.col1 = t2.col1

2nd colution: By using the MERGE STATEMENT

EXAMPLE:

merge tab3 as t3

using

(

select t1.col1,col2,col3 from tab1 as t1

join tab2 as t2

on t1.col1 = t2.col1

) as t4

on t4.col1 = t3.col1

when not matched then

insert(col1,col2,col3)

values(t4.col1,t4.col2,t4.col3);

PARTITION THE TABLE IN SQL SERVER

Hello Guys,

Main advantage of sql server is to maintain the huge data records.

A single table can hold unlimited records with 1024 columns for a DBA its very hard to maintain large number of records in a single table.

solution is single table can be spitted up in different number of partitions to maintain the database very easily.

EXAMPLE FOR PARTITION THE TABLE

use tempdb

--create partition function

create partition function part_fun(int)

as range

for values(10,20,30,40,50)

--create the parttition schema

create partition scheme part_scheme

as partition part_fun

all to ('primary')

---create the table with some records

create table parttiton_table

(

col1 int,

col2 nvarchar(max)

)

on part_scheme(col1)

insert into parttiton_table

select 1,'john' union

select 2,'john' union

select 6,'john' union

select 11,'john' union

select 17,'john' union

select 21,'john' union

select 25,'john' union

select 28,'john' union

select 34,'john' union

select 38,'john' union

select 41,'john' union

select 44,'john' union

select 47,'john' union

select 56,'john' union

select 58,'john' union

select 61,'john' union

select 66,'john' union

select 71,'john' union

select 77,'john'

select * from parttiton_table

--retrive the partition data from the table

select $partition.part_fun(col1) as partition_data,col1 as data from parttiton_table

order by partition_data

MERGE STATEMENT.....IN SQL SERVER 2008

Hi Guys,

I m here once again to explore a one specialized statement called MERGE STATEMENT.

Basically when we go for any DML operations, we have to write separate coding for each and every operation like INSERT,DELETE,UPDATE but in the MERGE STATEMENT having the feature to include all the three operations in a single logic and make the data to be updated when its matches and inserts when not matches.

One of the main advantage in the MERGE STATEMENT is data is to be read and processed only once in a pass.

MERGE STATEMENT SYNTAX:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]

[ OPTION ( [ ,...n ] ) ]

For Example:

use tempdb

GO

-- create the table with primary key

create table kingdom

(

id int primary key,

name nvarchar(20)

)

GO

--insert some records into the tables

insert into kingdom values(101,'king')

insert into kingdom values(102,'queen')

insert into kingdom values(103,'prince')

insert into kingdom values(104,'princess')

insert into kingdom values(105,'minister')

insert into kingdom values(106,'soldier')

GO

-- create another table with reference and insert some records into it

create table kingdom_details

(

id int references kingdom(id),

salary money

)

Go

insert into kingdom_details values(101,1000)

insert into kingdom_details values(102,850)

insert into kingdom_details values(103,700)

Go

--USING THE MERGE STATEMENT

-- DELETE THE RECORDS WHOSE SALARY GREATER THAN 900

--UPDATE THE RECORDS AND ADD 100 TO THE RECORDS IF EXISTS

--INSERT THE RECORDS

MERGE KINGDOM_DETAILS AS KD

USING (SELECT ID,NAME FROM KINGDOM) AS K

ON KD.ID = K.ID

WHEN MATCHED AND KD.SALARY > 900 THEN DELETE

WHEN MATCHED THEN UPDATE SET KD.SALARY = KD.SALARY + 100

WHEN NOT MATCHED THEN

INSERT(ID,SALARY) VALUES(K.ID,500);

GO

--retrive the original table records

select * from kingdom_details order by id

Query To Display The Foreign Key Columns In specific Database

hello Frds,

To Gain the better Performance of Sql Server.Generally we Go for the data to be Normalized means that splitting up the tables into several tables with some specific rules like primary key and foreign key references.

To find out the Foreign key relationships in between the tables of a database

Open the QueryEditor

---> use [database]

Go

select * from sys.foreign_key_columns

CHANGE TRACKING CAPTURE IN SQL SERVER 2008

Hi Frds,

This is one of the new feature introduced in SQL SERVER 2008.

Change tracking in sql server 2008 enables the applications to obtain only the changes made to the user tables along with the information of that particular changes. With change tracking integrated into sql server. In other words that it tracks the DML operations like(INSERT,DELETE AND UPDATE).

--Enabling the change tracking in Database level and table level

Alter database

Set change_tracking = ON(change_retention = 2 days,Auto_cleanup = ON)

Alter table

Enable change_tracking

With (track_columns_updated = ON)

Select * from sys.change_tracking_databases

Select * from sys.change_tracking_tables

Select * from sys.internal_tables where parent_object_id = OBEJCT_ID(‘tablename’)

Select CHANGE_TRACKING_CURRENT_VERSION()

Select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘tablename’))

Select * from changetable(CHANGES tablename,0) as CT order by sys_change_version

DECLARE @PreviousVersion bigint

SET @PreviousVersion = 1

SELECT CTTable.EmployeeID, CTTable.SYS_CHANGE_OPERATION,

Emp.FirstName, Emp.LastName, Emp.CurrentPayScale,

CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT

FROM CHANGETABLE (CHANGES Employee, @PreviousVersion) AS CTTable

LEFT OUTER JOIN Employee AS Emp

ON emp.EmployeeID = CTTable.EmployeeID

GO

Hope you Guys enjoyed the subject.

Without having the fun in the subject we cant gain the knowledge. Agree? haa!

Calling the Object(Stored Procedure) from a Linked server to Local SQL Server

hi Friends,

Today, I am going to share one of the most Interesting topic called LINKED SERVERS.

First of all what's it mean.

In the SQL SERVER one of the excellent feature called LINKED SERVER.The name itself indicating that the server can be add to a network group or single SQL SERVER, by stating the T-SQL commands we can the retrieve the data from the remote server databases, also we can combine the retrieved data with the local server data.

Basically we are having two ways to connect with the remote server.

Method 1: Turn your way to Object Explorer of sql server management studio

----->>> Click on the Server Objects to Expand there you can find the Excellent Option called LINKED SERVER,by Right clicking you can add NEW LINKED SERVER.

Method 2: By using the T-SQL commands of Stored Procedure we can connect to the Remote server.Open the Query Editor and Execute The commands.

sp_addlinkedserver,sp_addlinkedsrvlogin

That all Guys now I hope you guys get a basic idea about remote server(LINKED SERVER)

Now lets turn to our today s Goal 'CALLING THE OBJECT FROM A LINKED SERVER'

Two ways to crack the concept.

Case1: By using the four part Naming.

Syntax:

Exec [linked server].databasename.schemaname(database owner).storedprocedure 'params'

Case2:By Using Distributed Query Function(openquery())

Syntax:

select * from openquery([linked server].databasename.schemaname(database owner).storedprocedure 'params')

hope you guys got the basic idea about the LINKED SERVERS, like me.