for Sql server 2005, It was a bug when you modify maintainance plan you may get the error
“no description found” and you cannot edit the Maitainance plan.
problem could be the software you install is confilicting with sql server

to resolve this register following dll
regsvr32 msxml3.dll
regsvr32 msxml6.dll

Reference:
https://connect.microsoft.com/SQLServer/feedback/details/243850/ss2005-cannot-save-maintenance-plan-no-description-found-error?wa=wsignin1.0″
HTH
Vinay

When you configure the memory and on your system if other applications are also present, in such cases specially for 64 bit operating system the de-allocation of memory may impact huge performance issue. so lock page memory is useful here as to prevent/reserve the memory for Sql server.
1. Run “secpol.msc” from windows run dialog -Local Security Setting

2. Expand “Local Policies” in the left pane

3. Click “User Rights Assignment”

4. Double-click “”Lock Pages in Memory”

5. In the “Local Security Setting” page, please check if the account used by SQL Server in the list box

Following is the link which shows how to configure Lock page memory.

http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/06/25/lock-pages-in-memory.aspx

HTH
Vinay

It’s been quite some time that I blog, Sorry about that I was busy with my movement from USA to India and changed my job  part of life ,okay  back to India (Pune).

Lastly I wrote a blog on High Availability techniques here and How to setup simple Mirroring Here.  Continue on that following are some of the common difference of log shipping and Mirroring, actually both uses log and move the transaction “Mirroring used to call Real time Log shipping”. but the way it works makes it difference.

Hope this will help someone.

Thanx.

Vinay

Important: System Objects/Views/DMV/DMF

This is the some of the list of System Objects/Views/DMV/DMF.

Catalog View:

All system information is stored on catalog views which look like

Sys.xxx

Eg. Sys.databases

Compatibile views:

This are views to manage old compatibility

Select * from master.dbo.sysprocesses

Server Side DMV

1. Sys.dm_exec* (query exection)
2. Sys.dm_io* (io level)
3. Sys.dm_db* (database level  like index….)
4. Sys.dm_tran* (transaction level)
5. Sys.dm_os* (os level)

Component level DMV:

1. Sys.dm_Repl* (replication)
2. Sys.dm_broker* (service broker)
3. Sys.dm_fts* (full Text Search)
4. Sys.dm_clr*( CLR)

—————————– Understand the dmv:

Require permission as view database stats or view server stats

1.  select count(1) from sys.all_objects –1841

2.  select * from sys.databases

3.  select count(*) from sys.system_objects –1763

4.  select * from sys.sysobjects –- show all the objects in present database

5.  select * from sys.system_sql_modules where object_id=object_id(’sys.sysobjects’)—-shows code for system objects

6.  select * from sys.dm_exec_sessions -– like sysprocesses info

7.  select * from sys.dm_exec_requests order by logical_reads desc -– like sysprocesses info

select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) where session_id>50

8.  select * from sys.dm_os_waiting_tasks  — current waits

9.  select * from sys.dm_os_wait_stats  — report for waits

10.select * from sys.dm_os_latch_stats –report for latchs

11.select db_name(database_id),* from sys.dm_io_virtual_file_stats(NULL , NULL )

order by io_stall desc

12.select * from sys.dm_os_wait_stats

13.select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

14.select * from sys.dm_db_partition_stats

15.select * from sys.dm_exec_query_stats

Sys.dm_exec_query_stats:

Select * from Sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

cross apply sys.dm_exec_query_plan(qs.plan_handle)

16.select * from sys.dm_db_index_usage_stats

17.select object_name(object_id),* from sys.dm_db_index_physical_stats (

db_id(),null,null,null,default)  — index fragmentation information

18.select * from sys.dm_os_latch_stats

19.select * from sys.dm_db_missing_index_group_stats

1. select * from sys.dm_exec_query_optimizer_info
2. select * from sys.dm_exec_cached_plans

22.select * from sys.system_internals_allocation_units  — inside information about object first root and next page address.

1. select * from sys.system_internals_partitions  –more inforation abt views useful for join of system_internals_allocation_units

22, 23 used to be achieve using DBCC IND for DBCC Page() with dbcc traceon(3604)

1. select * from sys.dm_io_pending_io_requests  — any pending io
2. select * from sys.dm_tran_locks  –lock info
3. select * from sys.dm_tran_active_transactions
4. select * from sys.dm_os_sys_info  –sys info
5. select * from sys.dm_os_performance_counters  –perfmon info
6. select * from sys.dm_os_schedulers  –CPU info
7. select * from sys.dm_os_ring_buffers  –memory
8. select * from sys.dm_os_buffer_descriptors  –memory

32.select * from sys.dm_os_memory_cached_counters  –Sql server 2008

33.select * from sys.dm_os_sys_memory –Sql server 2008

DBCC MEMORYSTATS

Thanx.

Vinay

Reference:

http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx

Today while being on forum found this information so though to blog for future reference. thanx Uri Dimant for sharing this. 

WITH cte 

AS 

( 

SELECT object_name(ic.object_id) as object_name , index_name = i.name,
‘column’ = c.name,
        ‘column usage’ = CASE ic.is_included_column
                    WHEN 0 then ‘KEY’
                    ELSE ‘INCLUDED’
        END
FROM sys.index_columns ic JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
JOIN sys.indexes i
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id 

) SELECT * FROM cte WHERE object_name =’tablename’ 

——————————– 

Below is the query which give information about parallel queries: 

–This first thing to check if CPU is at 100% is to look for parallel queries: 

– Tasks running in parallel (filtering out MARS requests below): 

select * from sys.dm_os_tasks as t  

 where t.session_id in ( select t1.session_id from sys.dm_os_tasks as t1  group by t1.session_id having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id)); 

– Requests running in parallel: 

 select * from sys.dm_exec_requests as r  

 join (select t1.session_id, min(t1.request_id) 

 from sys.dm_os_tasks as t1  

 group by t1.session_id  

 having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id) 

 ) as t(session_id, request_id) 

 on r.session_id = t.session_id  

 and r.request_id = t.request_id; 

 Thanx. 

Vinay 

Reference: 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/383c0712-9416-4e23-9f45-d74148202596 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e5a72c22-56c9-420a-bf2f-05b834665e41 

Introduction to Memory configuration

Yesterday I was discussion with one of my friend on memory management and internals to expertise in sql server. So started working more on it and found very fundamental information everyone should aware of it. Memory management for MS sql server is very important. We should have a basic knowledge of memory information, how it works and how to configure to effectively usage of it. Memory is a common resource of the system.

Memory stores everything about sql server.

For 32 Bits system(x86):-

Memory has a limit of 4GB x86 (32bit 2³²) of VAS (Virtual Address Space) which divides as 64KB for Null blocks which stores NULL Pointers, 2GB is for USER MODE and 2 GB for Kernel Mode. This is the default behavior of the memory configuration.

We can increase the USER mode to 3GB by adding /3GB option with /USERVA at BOOT.ini file till Windows server 2003 and earlier version, for Windows server 2008 is BCDEdit.exe. This will be good for USERMODE but restricting KERNEL mode to 1GB which may impact performance somewhere.

What if we wanted to use more than 4GB of memory? For Windows to recognize more than 4GB of memory we have to enable PAE option. PAE option will increase the system to recognize more than 4GB of memory, which is up to 64GB for x86.

Now our system has more than 4GB of memory, but for MS Sql server to use more than 3GB of memory requires to enable AWE configuration option. Using AWE option Sql server will recognize that extra memory enabled by PAE. This extra memory is only good for database cache pages not procedure cache.

USER Mode of Memory is divided into two parts:

1. MemToLeave (Stack Size  * Max Worker Thread ) + (-g startup option)

Default values

Stack size =512k

Max worker thread = 256k

-g option =256mb (we can change this)

1. Buffer Pool (USERMode (2GB) – MemToLeave).

For 64 Bits system:-

The system is having same partitions but here the system has huge capacity for the system. 2 64. so here USER MODE could be 2GB to 8TB which is very high and generally nobody uses this. And Kernel Mode uses up to 8TB, means for 64 Bit system there is no upper limit for memory. Hence no need for PAE option. But we can use AWE ability to increase the VAS is used.

WOW (Windows on windows)

System, which has 32 Bit Sql server on 64 Bit Windows system(WOW). We can have 4GB of USER Mode space. And still use AWE for extra USER Memory.

Additional information:

Buffer Pool Stores:

>>  Sql server connection requires 32 bits of Memory

>>  Lock requires 96 Bytes of memory.

>>  Data Pages

>>  Compile plan

>> Execution Plan

>> Workable

As this is my first blog exclusive on memory, will try to write some more blogs on Memory in future.

Thanx.

Vinay

http://vinay-thakur.spaces.live.com/blog/

http://rdbmsexperts.com/Blogs/

https://twitter/thakurvinay

Reference:

http://blogs.msdn.com/slavao/archive/category/9005.aspx

Bob Ward’s PASS Webcast.

Yesterday I though of doing an audio webcast what all I blog.

So started audio webcast, its a series of webcasts on Whats New blog category which I blog here and here please refer blog when listen.

Today I uploaded it on public folder. Hope you will enjoy it same as I did while recording.

Following are the audio webcasts and their link to it

1. History of MS Sql Server

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma

2. Whats New MS Sql Server 7

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!744

3.  Whats New in MS Sql Server 2000

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!746

4. Whats New in MS Sql Server 2005

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!747

5. Whats New in MS Sql Server 2008 & MS Sql Server 2008 R2

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!748

Please let me know your feedback on this.

Please read the Blog as a reference for this Audio Webcast.

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dWhats%2520New

and

http://rdbmsexperts.com/Blogs/?p=57

Thanks and Happy Learning.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs

vinay-thakur.spaces.live.com/

twitter/thakurvinay

continue with performace tunning stuff, I would like to make a checklist (kind of) for general performance tunning or dba checklist.

I highly recommend to read my earlier post on performance tunning here

Performance Monitoring (Activities):

1. Dashboard report (2005) Data collector (2008)
2. Performance Monitor (Counters-CPU/Memory/IO/Network/Sql Level)
3. Profiler Utility trace/ Server Side trace….. (can insert into table and analyze)
4. Periodically snapshot for Stats (waits and file stats) and other DMV’s
5. Using DMV to find the query which needs to optimize.
6. Use of Database Tuning Advices
7. Analyze the query compilation plan and time for compilation/execution
8. Check the index /statistics
9. Check the tempDB bottleneck

Other DBA checks:

1. System configuration
2. Check jobs – Backup/reindex/statistics
3. Check disk space
4. Check Error log (can insert into table and analyze)
5. Check Event Viewver

Other checks specific to the components like High Availability and other components is not discuss here.

Will blog in detail of each one.  also try to keep  updated this blog as and when require…. appreciate your comments on this. please help me to make this list better.

Thanx.

Vinay

TempDB Internals:

TempDB is very important DB in sql server, generally sql server automatically takes care of the storage management of the system, and we do not require bothering much about this storage, also now days we are having good/much resources (IO/CPU/Memory) so we don’t need to think much about the TempDB usage. But for the system which has very heavy activity and huge database size may have to consider the usage of tempDB and may be sometime tempDB goes out of disk. Especially after Sql server 2005 were there was a huge improvement at system includes versioning which requires heavy usage of tmepDB.  Hence now a days its very important for everyone to know what is tempDB stores and how it works ,Also what all general recommendation/consideration about tempDB.

TempDB has following properties:

1. This is the System database can only be “Simple” Recovery mode.
2. Every Sql server instance is having only one TempDB Database
3. Auto creates same as Model whenever sql server restarts
4. Initial size of TempDB data file is 8MB and log file is 512KB
5. Does not allow Auto shrink.
6. TempDB will have only one filegroup but can add any number of files.
7. Stores all temporary data means all data will lost once sql server stop/restarts
8. Cannot drop the tempDB database and cannot change the mode of if(readonly/emergency/Offline…)
9. Database snapshot is not supported.
10. Cannot take backup of TempDB.
11. Database checksum is not supported.
12. Objects of TempDB can be cached : if we have small temp objects that can be cached due to drop and create of frequent table (size is less then 8mb).
13. Log growth always on disk whereas for small data it keeps on memory for tempdb
14. For tempdb Log file “REDO” operation not required. After value is not logged(eg update x set y=’abc’ where y=’xyz’ here tempdb does not log ‘abc’)
15. Instance file initialization, when autogrowh happens space of growth is “Zeroed” them out. Make autogrowh fast but could cause security issue-

TempDB stores:

a)      Temporary objects

b)      Internal Objects

c) Version Stored Objects – New in sql server 2005

a) Temporary objects:

Temporary objects are objects which is created temporary using #, ## or veritable @, @@  etc.

eg. If you create a temp table #abc

create table #abc (a int,b varchar(8000),c varchar(8000))

Irrespective of the database you are using this table will create at tempDB database

select * from tempdb.sys.all_objects where name like ‘%abc%’

You will see the object name something like this

#abc________________________________________________________________________________________________________________000000000006 (xxx –connection number)tab

So always remember when you create any temporary objects there is a cost of it as they reside at tmepDB database.

b) Internal Objects:

This is very important thing and generally most of the Developers/DBA does not aware of this. Internal objects are not visible but it uses tempDB storage. When we run a query which requires temporary object to be created to perform that operation is created in tempDB.

Eg. Restart the sql services. And check the size of TempDB data files.

Generally it will be 8mb.

Now suppose you have one big table. Here I am using charge table from credit database download from www.sqlskills.com

select * from charge order by category_no

Now once you run above select statement the size of tempDB data file grows to appx 80MB (wow) …. Howz that. As “order by” clause require creating a temp object for ordering the records from charge table because category_no does not have index on it (not ordered).

There are some other operations which generates internal objects are: Hash join, hash aggregates, DBCC check, Order By etc.

c) Version Stored Objects:

Sql server 2005 has improved the engine and enables the row versioning; Following are some of the features which use row versioning.

>> Snapshot Isolation.

>>Triggers:

In previous version of Sql server 2000 and earlier triggers used to store into log file. On sql server 2005 onwards it keeps into TempDB. After trigger store Versioning.

>>Online index Build:

When online index rebuild and at the same time any DML statements happens it stores as versioning.

Recommendations:-

For Sql Server 2005 multiple data files would be good to improve the performance of TempDB.

For 2000 number of data file better to be good as number   of CPU.

Set 80% of estimated growth, and let 20% be handed by auto growth.

TempDB Shrink can be done  at startup parameter –c –f this will start the sql server with minimum tempdb size.

Errors:

1101 or 1105: no space in tempDB

3959: Version store is full (after 1101 or 1105)

3967: Version store is force to shrink

3958 or 3966: Transaction cannot find required version record.

Troubleshooting tempDB issue:

Generally TempDB issues are TempDB FULL and tempDB performance.

Generally Tempdb will have two issues

TempDB Full:

To troubleshoot the tempDB issue we have two dmvs:

Sys.dm_db.file_space_usage

Sys.dm_db.trask_space_usage

Sys.dm_db_session_space_usage

This issue occurs due to:

Query used in application requires heavy internal objects tempdb – to solve that use nested look join instead of hash join/aggregates can be achieve using creating proper indexes on the table or use hints.

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue

Also if require don’t use snapshot isolation level, and things which usages tempDB.

Hope this will help someone to understand more about tmepDB.

Thanx.

Vinay

Reference:

Practical Troubleshooting –Ken Henderson.

http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://msdn.microsoft.com/en-us/library/cc966545.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx

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

As discussed about Disaster Recovery and High Availbility topic here continue on this. today would like to discuss some more information about mirroring.

As stated in my ealier post about mirroring, today I would like to write about how to conifigure mirroring, mirroring is very simple to configure. before going for it we should deside what time of mirroring we want to go with

High Availability

High Protection

High Performance

depending upon that we have to configure Witness.

I would say there are only two steps for it.

1. Create End points

2. alter database “” set PARTNER

this is the simple procedure I want to discuss there are other several ways to setup the mirroring.

1. need to decide what is your “priceple” database server , “Mirror” database server and “Witness” database server. this can be seperate box or difference named instance on the same server, but should be on differenct instance as on one  sql server you can create multiple “endpoints” which is the way Mirroring works(interacts)

the simple command for creating endpoints could be

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT =5022)
FOR DATABASE_MIRRORING (ROLE = ALL )
if you are on same server(box) with different instances then we have to give different port number(eg. 5023, 5024)

* Principle and Mirrored database should be having same edition of sql server. witness can be express edition, and should be of “FULL RECOVERY MODEL”

Backup the Principle and restore it on Mirrored database with NORecovery
once you create the “Endpoints” then we have to alter the database PARTNER to listen to that endpoint port like.

at principle

alter database MirrorDB  set Partner=’TCP://MirroredServer:5022′

at mirror server

alter database MirrorDB  set Partner=’TCP://PricipleSrv:5024′

we can check the property of the principle database of option “Mirror” you will see the end point specified and other mirroring related info.

you are done. this is quick and short way of configuring mirror, once priciple is down it will rollover to mirror database server and Mirror server will become priciple and when Priciple is up that will become “mirror” server ie. reverse the setup.

*You cannot mirror the master, msdb, temp, or model system databases.

You can add on the securites and other configuration on it.

This is what I learned today.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/blog

twitter\thakurvinay