SQL SERVER
Audit database with DBCC & sys.dm_exec_requests (Tips)
You can follow different aspects of your database, put the focus on the following subjects, for example :
– Ensure that the index and data pages are correctly linked.
– Pointers are consistent.
For this used the command DBCC with type CHECKTABLE.
DBCC CHECKTABLE ('Clients') WITH PHYSICAL_ONLY
GOHowever you can edit different aspects, below a link that describes every possible aspect was audited
Link : https://technet.microsoft.com/en-us/library/aa258281(v=sql.80).aspx
We will change perimeters we will look a blocked processing, suspended, however, the proposed solution remains little used, unlike sp_who2 ou sp_who,
This is the table which must be selected : sys.dm_exec_requests.
SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended'; GO
GC.Collect
Tips
MAILING SQL SERVER
Below a post aimed to alert you if a volume is exceeded on your SQL Server, for example, the basic transaction data may exceed certain thresholds for that access your agent SQL SERVER, configure it in such a way as to send mails.
Audit SQL SERVER
You gave rights to a user, you want to know if that user has rights has spread another user, simple to make, use SQL SERVER Audit, create an audit specification
“SERVER_ROLE_MEMBER_CHANGE_GROUP”
You use both nodes Audit (audit and Create output formatting) & Specification (Create the specification)
Link : https://technet.microsoft.com/en-us/library/cc280500(v=sql.105).aspx
For your security management of your database, choose the concept of Role personalized, permission on a schema, add users to your role (Just as it would have been done with the Principal, Identity, Permission, Claims in C # ). Associating the scheme now to the role and not to different users.
Compatibility Level (Tips SQL Server)
This post is intended to include the steps to migrate and change basic version of SQL SERVER 2005 data on 2014
It’s simple to make a BACKUP of Full aging database (SQL 2005), do the RESTORE of the database on the target server (Data Log & Database).
However consider changing the COMPATILITY LEVEL on the 2014 base,
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 | 120 }
Note : 120 is the compatibility level for SQL SERVER 2014
Link : https://msdn.microsoft.com/fr-fr/library/bb510680(v=sql.120).aspx
Policy Management & Tips
It is interesting how implement code standards for stored procedures in Sql Server, stored procedures system are prefixed with “sp_”, for our user stored procedures, let the prefix of “usp_”, this is done with Policy Management (see SSMS – the object explorer, root).
Right-click on Policy Management and select New Condition, Type a name and condition as well as the body of your condition, we have decided to select the Name filter expression LIKE (‘usp_%’).
The next step is to create a policy associated with this codition, right-click Policy Management and select New Policy, select the Check Condition list, the condition created previously, the evaluation mode must be set to “On change: prevent prevention “, the default mode is” on Demand “and policies can not be enabled in this mode, for the Target, make sure you select all the databases and all stored procedures.
SQL Server – Slow Finish
As part of optimizing your SQL queries, if you want to line of code to include the execution plan, drag this instruction before execution.
SET STATISTICS SHOWPLAN_XML ON
If you want to optimize the recovery of your data in an environment with a default isolation level of Read Committed or SERIALIZBLE, slide this instruction before execution.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
As part of query optimization, you can associate a clean SQL query execution plan for you so to override the behavior of the query optimizer, see reference Plan in SQL Server Management Studio, you can create your SEEK FORCE clauses on your favorite index etc.
link : https://technet.microsoft.com/fr-fr/library/bb895390(v=sql.105).aspx
You can sometimes just be an update statistics 🙂
For the database novice, the positioning of the index is very important and is based on real data collecting tools (Production Environment)
As for the SQL Server after try: Database Engine Tuning Advisor, included in SSMS since the 2012 version.
GC.Collect
Astuces SQL Server
This note aims to put the focus on database issues
How to increase the performance of a view by saving the data to disk?
Create a cluster view (clustered).
What is XACT_ABORT?
cancel or not a transaction inside stored procedure, for example, if an error occurs at a treatment SET XACT_ABORT ON
How to solve the fragmentation problem index?
Use Rebuild or Reorganize index
The result is obtained with function sys.dm_db_index_physical_stats.
Below results :
avg_fragmentation_in_percent : The percent of logical fragmentation (out-of-order pages in the index).
fragment_count : The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages : Average number of pages in one fragment in an index.
If avg_fragmentation_in_percent is greater than 5% and less than 30%
=> ALTER INDEX REORGANIZE
If avg_fragmentation_in_percent value is greater than 30%
=> ALTER INDEX REBUILD WITH (ONLINE = ON)
