SelectEtoile : Base de connaissance SGBD

Posts Tagged 'sql'

SQL Workbench

Installation de SQL WorkBench

 

Présentation de l'outil

Voici les différentes fonctionnalités de cet outil :

Requêteur SQL

Permet de comparer différents schémas de bases de données

Extraction des scripts des créations des objets ou d'une base de données

Outil de recherche avancé (recherche de mot dans les objets, recherche d'un id dans un table avec correspondance...)

Outils d'extraction des données et même d'insertion (On peut par exemple extraire d'une base pour insérer dans une autre)

 

Cet outil est multi SGBD.

 

Installation

Pour télécharger, http://www.sql-workbench.net/downloads.html

Une simple décompression suffit au déploiement de l'outil

 

 

Paramétrage

Par défaut, les fichiers de conf sont dans $HOME :

  • workbench.settings : configuration de l'outil (ex : taille de la fenêtre...)
  • WbProfiles.xml : permet de créer une liste de serveurs prédéfinie. Ce fichier n'est pas créé par défaut.

Exemple de conf :

 <void method="add">
   <object class="workbench.db.ConnectionProfile">
    <void property="driverName">
     <string>Oracle</string>
    </void>
    <void property="driverclass">
     <string>oracle.jdbc.OracleDriver</string>
    </void>
    <void property="group">
     <string>ORACLE</string>
    </void>
    <void property="name">
     <string>InstanceName</string>
    </void>
    <void property="storePassword">
     <boolean>false</boolean>
    </void>
    <void property="storeExplorerSchema">
     <boolean>true</boolean>
    </void>
    <void property="url">
     <string>jdbc:oracle:thin:@hostname:port:InstanceName</string>
    </void>
    <void property="useSeparateConnectionPerTab">
     <boolean>true</boolean>
    </void>
   </object>
   </void>

 

  • Autre fichier

 

Lancement de SQL WorkBench

  • En mode console : SQLWorkbench.exe
  • En mode ligne de commande : sqlwbconsole.exe

 

 

 

Articles tagged

Comment récupérer une base à partir du fichier mdf ?

1 Faire une copie des fichiers encore présents de la base de données.
2 Procéder à la création d'une nouvelle base de donnée
Elle devra porter les mêmes noms de fichiers ( .MDF et .LDF) que l'ancienne
3 Stopper le service SQLServer
4 Détruire les fichiers de la base nouvellement créée.
5 Renommer les premiers fichiers de base pour qu'ils correspondent à ceux de la base précédemment créée
6 Redémarrer le service SQLServer.
A ce stade la base de données devrait être active et fonctionner.
Si par contre, elle apparaît en suspect il ne faut pas s'inquiéter. Il faut procéder aux étapes suivantes.
7 Avec l'analyseur de requêtes, se connecter sur le serveur sur la base master et effectuer les commandes suivantes

sp_configure 'allow updates',1
reconfigure with override
UPDATE sysdatabases SET status=32768 WHERE name='bdName'

8 Stopper le service SQLServer.
9 Renommer le fichier Log en .old (ou toute autre extension)
10 Redémarrer le service SQLServer. ( Si ce n'était pas déjà le cas, la base apparaît en 'suspect')
11 DBCC rebuild_log ('bdName','chemin complet et nom du fichier log à reconstruire')
12 UPDATE sysdatabases SET status=0 WHERE name='bdName'
13 Puis pour finir

DBCC checkdb ('bdName ')
GO
DBCC newalloc ('bdName ')
GO
DBCC checkcatalog ('bdName ')
GO

Ceci pour vérifier la cohérence de la base de données

bcp --initstring

Cool

A partir de la version 15 du client ASE, un nouveau paramètre du bcp permet d'initialiser le bcp avec des commandes SQL.

bcp ... --initstring <SQL statement>

 Exemple:
bcp pubs2..titles in titles.txt –-initstring “set replication off” 
 
 

 

Articles tagged

Trouver le compte de service d'une instance MSSQL

DECLARE @ServiceaccountName varchar(250)
DECLARE @CurrentSet varchar(250)
if @@SERVICENAME = 'MSSQLSERVER'
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\'+@@SERVICENAME
else
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\MSSQL$'+@@SERVICENAME
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@CurrentSet,
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'
 
SELECT @@servername + ' - ' + @ServiceaccountName

Les liens pratiques de SQL Server

Cet article contient une liste de sites web pratiques pour l'administration SQLServer

 

Les docs Microsoft SQLServer

Les vidéos microsoft : http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

 

Gestion du journal de log

Log Management :
http://www.sqlservercentral.com/articles/Stairway+Series/94552/
http://www.sqlservercentral.com/articles/Transaction+Logs/71415/

Log Tuning : http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/

 

Monitoring :

SQLServer Creation d'une alerte/montoring SQLServer : http://www.sqlservercentral.com/articles/SQL+Monitoring/100615/

 

Les partitions

Gestion des partitions : http://www.simple-talk.com/content/article.aspx?article=1587

Ex d'un switch et split de partition : http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/08/13/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i-split-it-with-minimal-io-impact.aspx

 

Les indexes

https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/?utm_source=ssc&utm_medium=publink&utm_content=indexingquestions

 

Trace Profiler

Mise en place de trace profiler sous SQL SERVER : http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

 

AlwaysOn :

http://blogs.msdn.com/b/sqlcat/archive/2014/02/03/alwayson-availability-groups-listener-named-instances-port-numbers-etc.aspx

 

Performances Monitoring :

http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

https://www.simple-talk.com/sql/database-administration/baselining-with-sql-server-dynamic-management-views/

https://www.simple-talk.com/sql/database-administration/eight-steps-to-effective-sql-server-monitoring/

 

Pratique

Comment regrouper un RTM et un SP2 sous SQLServer 2008 (slipstream) : http://blogs.msdn.com/b/petersad/archive/2010/10/14/creating-a-merged-slipstream-drop-containing-sql-server-2008-rtm-and-service-pack-2.aspx

Faire de la pagination sous SQLServer : http://www.databasejournal.com/features/mssql/paging-data-with-tsql.html

 

Change Data Capture

http://www.databasejournal.com/features/mssql/getting-starting-with-change-data-capture-cdc-in-sql-server-part-1.html

Les deadlocks

Surveiller les deadlocks : http://www.opsvault.com/how-to-monitor-deadlocks-in-sql-server/

Des exemples tres detaillés : https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/?utm_source=ssc&utm_medium=publink&utm_campaign=simpletalk&utm_term=additionalarticle

 

Tout sur la base tempdb :

tempdb contention : http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

 

Hashage / Cryptage :

http://blog.developpez.com/sqlpro/p12496/langage-sql-norme/hachage-nest-pas-cryptage-de-la-securite-des-donnees-chiffrees-dans-les-sgbdr

 

Encryption / Chiffrement...

http://www.dbi-services.com/index.php/blog/entry/transparent-data-encryption-key-management-and-backup-strategies

 

Les traceflags :

Les traceflag sous SQLServer : http://www.sqlservercentral.com/articles/trace+flags/70131/

 

Sauvegarde et restauration

Backup / restore avec powershell : https://www.simple-talk.com/content/article.aspx?article=1802

 

SQLServer 2014 :

New Features : http://www.databasejournal.com/features/mssql/whats-new-with-sql-server-2014.html

AlwaysOn 2014 : http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/27/alwayson-in-sql-server-2014-ctp1.aspx

 

Performances sur les disques :

SSD : http://henkvandervalk.com/maximizing-sql-server-2008-r2-table-scan-speed-from-dsi-solid-state-storage

Tester ces io : https://www.simple-talk.com/sql/database-administration/the-sql-server-sqliosim-utility/

 

Hekaton :

Procedure stockée en code natif : http://msdn.microsoft.com/fr-fr/library/dn424864.aspx

gestion des tables Hekaton : http://conseilit.wordpress.com/2013/07/01/sql-server-2014base-de-donnes-et-premire-table-hekaton/

http://blog.developpez.com/mikedavem/p12490/sql-server-2014/tables-in-memory-indexes-hash-et-paramtrage-du-nombre-de-buckets

 

Perfs et tuning :

Parameter sniffing : http://www.sqlservercentral.com/articles/Parameter+Sniffing/98481/


Gestion des licences

http://www.sqlservercentral.com/articles/108317/

http://conseilit.wordpress.com/2011/11/04/licences-sql-server-2012/
http://blogs.msdn.com/b/editeurs_de_logiciels/archive/2013/10/30/licensing-comprendre-le-mode-de-fonctionnement-des-licences-sql-server-2012.aspx

 http://conseilit.wordpress.com/2011/11/04/licences-sql-server-2012/

 

Quelques outils utiles

SQL Query Stress :

http://www.mssqltips.com/sqlservertip/2730/sql-query-stress-tool/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717

 

 

http://sqlquerystress.software.informer.com/download/

 

 

Quelques exemples pratiques de code SQL sous SQLServer :

Les fonction window in :

https://www.simple-talk.com/sql/t-sql-programming/window-functions-in-sql/

https://www.simple-talk.com/content/article.aspx?article=1927

 

Des scripts SQL et powershell :

http://gallery.technet.microsoft.com/scriptcenter/site/search?f[0].Type=RootCategory&f[0].Value=databases&f[0].Text=Databases&f[1].Type=SubCategory&f[1].Value=sqlserver&f[1].Text=SQL%20Server

 

Les sites et blogs utiles :

http://thesqlagentman.com/

http://drsql.org

http://www.simple-talk.com/

http://thomaslarock.com/

http://dba.stackexchange.com/

http://brentozar.com

http://www.scarydba.com/

 

SQL Server et les partitions

Vous trouverez ici quelques scripts sql permettant de visualiser et manipuler les partitions sous SQLServer

Les tables systèmes :

  • sys.tables
  • sys.indexes
  • sys.partitions
  • sys.partition_schemes
  • sys.partition_functions
  • sys.partition_parameters

 

Lister les partitions d'une base :

select object_name(object_id) as tablename,* from sys.partitions



Lister les partitions de toutes les tables

select T.name, T.Object_id, T.Type_Desc,P.Partition_Id
from sys.tables T
inner join sys.partitions P on T.object_id = P.object_id

 

Affiche la fonction et le schéma de partition de toutes les tables :

SELECT
    tbl.name AS [Name],
    tbl.object_id AS [ID],
    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],
    CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END AS [FileGroup],
    CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
    ps.name AS [PartitionScheme],
    pf.name    AS [PartitionFunction]
FROM
    sys.tables AS tbl
    INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
    LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
    LEFT OUTER JOIN sys.partition_schemes AS ps ON dsidx.name = ps.name    
    LEFT OUTER JOIN sys.partition_functions pf on ps.function_id=pf.function_id



Lister les caractéristiques des partitions d'une table

select object_name(object_id) as table_name, index_id, partition_number, rows
from sys.partitions  where object_name(object_id) = 'matable'

 

Afficher le détails des fonctions et schémas de partitions

-- Affiche les schémas et fonctions de partitions

select s.name as scheme_name, f.name as function_name, limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id

 

-- Liste des paramètres et des valeurs de la fonction pfDate

select    *
from    sys.partition_functions pf
        inner join sys.partition_parameters pp on pf.function_id=pp.function_id
        where pf.name = 'pfDate'

select    *
from    sys.partition_functions pf
        inner join sys.partition_range_values prv on pf.function_id=prv.function_id
        where pf.name = 'pfDate'

 

--relation scheme fonction valeurs

select    s.name as scheme_name, f.name as function_name,
        limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END ,
        v.boundary_id, v.value
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id
inner join sys.partition_range_values v on v.function_id=f.function_id

 

 

 

Autocommit contre Transaction explicite sous SQLServer

Le but de l'article est de démontrer la différence de performance entre une mise à jour faite en commit implicit ou autocommit et une transaction explicite.

C'est un exemple un peu extrême mais ça montre bien la différence de temps.

 

Commençons par se créer une petite table :

create table matable (i int,c varchar(50))
create clustered index ind on matable(i)
set statistics io,time on
go

 

Code de la requête avec AUTOCOMMIT :

-- AUTOCOMMIT ou COMMIT IMPLICIT
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
declare @i int
select @i=0
WHILE @i < 60000
Begin
INSERT INTO matable values (1,'Name')
select @i=@i+1
End
select * from sys.dm_os_wait_stats s where s.wait_type='WRITELOG'

Temps d'execution : 40 secondes

Résultats :

wait_type      waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms
WRITELOG    60045                       34054              260                         671

 

Code de la requête avec TRANSACTION EXPLICITE :

-- EXPLICIT TRANSACTION
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
declare @i int
select @i=0
BEGIN TRAN
WHILE @i < 60000
Begin
INSERT INTO matable values (1,'Name')
select @i=@i+1
End
COMMIT TRAN
select * from sys.dm_os_wait_stats s where s.wait_type='WRITELOG'

Temps d'execution : 10 secondes

Résultats :

wait_type      waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms
WRITELOG    1                              5                     5                             0

 

On va donc 4 fois plus vite avec une transaction explicite qu'en AUTOCOMMIT.

Cela s'explique très bien avec les temps d'attentes sur la Wait Task 'WRITELOG'. En effet, lors de la 1ère requête, on passe 34 secondes sur 40 à écrire dans le log.

'WRITELOG' indique que SQL serveur attend que les opérations IO relatives au transaction log soient terminées.

Il est généralement bon indicateur d'un problème de performance disque.

 

J'espère que ca vous a plu.

 

 

Débuter sous SQL Server Cluster

Quelques documentations super interressantes pour commencer :

 

  • Installation of SQL Server 2005/2008 clusters on Windows 2003/2008 : clique ici
  • Installation of SQL Server 2008 clusters on Windows 2008 Step 1 : Validation wizard : clique ici
  • Ressources disques d'une instance SQL en cluster : clique ici

 

 

 

Analyser une requete sql SYBASE ASE

Les outils présentés ci-dessous permettent de tracer et d'analyser une requête sql sur un serveur SYBASE ASE.

 

  • set showplan on : affiche le plan d'exécution de la requête
  • set statistics io,time on : affiche le nombre d'io/ table/index et le temps des différentes étapes
  • set noexec on : combiner avec showplan, permet d'afficher le plan sans executer la requête
  • set tracefile dirname/filename [for spid] : redirige les résultats des ordres SQL d'un session vers le fichier spécifié (New ASE15)
Toujours faire un 'set tracefile off' en fin de session
Sinon, il est possible que toutes les autres sessions soient polluées par cette trace
  • set show_sqltext {on | off} : affiche le SQL text d'un requête, procédure stockée, curseur ...
  • dbcc traceon(3604) : affiche la sortie à l'écran. Il faut positionner cette option pour les commandes suivantes
  • dbcc traceon(302) : affiche le cout des jointures et des SARG sur chaque table
  • dbcc traceon(310) : affiche les plans de moins en moins couteux (NEW PLAN). Par contre, il n’affiche pas les plans de plus fort cout.
  • dbcc traceon(317) :  affiche les plans plus couteux que les NEW PLANS. Ce sont les WORK PLANS.
  • dbcc sqltext(spid) : affiche la requête lancée par le spid

 


Différents paramètres de 310 et 317:

  • total cost : estimation du cout total en millisecondes d’une jointure (lp*2ms+pp*18ms)

  • varno : position de la table dans la clause from (NB : max 16 tables / jointure, si varno > 16 worktable)

  • indexid : indid de l’index utilisé (0 si la table)

  • pathtype : type d’opération (sclause, join ou orstruct)

  • method : NESTED ITERATION (en majorité), REFORMATTING ou OR OPTIMIZATION

  • outerrows : nombre de lignes de la table externe pour chaque ligne de la table courante

  • joinsel : estimation de la selectivité de la table ou de l’indexe de la table courante

  • cpages : nombres de pages (donnees ou indexes) que l’optimiseur estime dans la table courante

  • prefetch : prefetch strategy (N : pas de prefetch, S : prefetch strategy). Important quand le server est configuré en Large IO.

  • Iosize : taille des blocks io choisi par l’optimiseur

  • Replace : strategie de replacement dans le cache (LRU ou MRU)

  • Lp : Nombre de logical reads = outerrows * cpages

  • Pp : nombre de physical reads

  • Corder : principale (premiere) colonne de l’indexe (colid de syscolumns)

  • Jnvar, Refcost, Refpages, reftotpages, ordercol[0] sont peu utilisables et peu importantes

 

 

Si vous avez un doute sur une requête, il est possible de forcer le plan d'exécution ou de forcer un index précis.

Cela est très utile si on pense qu'un index ou un plan est meilleur que ce que l'optimiseur SYBASE a choisi

 

Pour forcer le plan, il suffit de lancer la commande suivante :

set forceplan on -- avant de lancer la requête

Pour forcer l'index, il suffit de préciser entre parenthèses "index nom_index" comme indiqué dans l'exemple suivant :

select * from ma_table (index mon_index) where col1 ='00'

 

Le forceplan force l'optimiseur a choisir son plan dans l'ordre des tables de la clause FROM. L'optimiseur choisit toutefois les index qu'il peut prendre sauf si vous les avez forcer aussi.

 

Informations pratiques :

Si une valeur d’une colonne est plus importante que les autres (ex : 90 %), les jointures sur cette colonne (ex : valeur1 représentant 0.001 % de la table) ne prendront pas forcément le bon index.

En utilisant la commande sp_modifytats comme suit, cela permet de modifier l’indice de densité de la colonne et à l’optimiseur de prendre un meilleur indexe.

 

Sp_modifystats tablename,colonnename,’REMOVE_SKREW_FROM_DENSITY’

 

Lien très intéressant sur le sujet : http://www.sybase.com/detail?id=2602#539757

 

 

Requetes divers sur SYBASE ASE

Autojointure à regroupement :

SELECT col1, col2, count(*)

FROM tab

GROUP BY col1, col2

ORDER BY col1, col2

compute sum(count(*)) BY col1

compute sum(count(*))

 

 

Modification format de date :

 

02/02/02 en 02-02-02 :

select object_name(id), stuff(stuff(convert(varchar, moddate, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), moddate, 108),

 

 

Feb 7 2005 12:53PM en 20050207121240

convert(varchar, moddate, 112) + substring(convert(char(8), moddate, 108) , 1, 2) + substring(convert(char(8), moddate, 108) , 4, 2) + substring(convert(char(8), moddate, 108) , 7, 2)

 

 

 

Commandes sur la gestion des dates

2 format de data différents

declare @dat datetime, @dat2 smalldatetime
select @dat = getdate()
select @dat2 = getdate()
select @dat, @dat2


Difference entre 2 dates :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select datediff (hh,@dat1,@dat2)


Ajout 1 heure :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select dateadd (hh,+1,@dat2)


Affichage avec un format particulier

select convert (char(26),getdate(),108)

 

Affichage de la date du jour :

- 3 mois : select convert (smalldatetime, Dateadd (mm, -3, getdate()))

- 2 jours : select convert (smalldatetime, Dateadd (dd, - 2, getdate()))



Mise a jour des threshold

select "use "+db_name(dbid)+char(10)

+"go"+char(10)+

"sp_addthreshold @dbname='"+db_name(dbid)+"',@segname='logsegment',@free_space="+ convert( char(8), sum(size)*10/100)

+" , @proc_name='sp_thresholdaction' "+

char(10)+"go"+char(10) as "--seuil 30%"

from master..sysusages where segmap=4 group by db_name(dbid)



Utilisation de patindex et substring :

extraire dans S_AnoDescription, les 50 caractères après la chaine @ModuleName.

select distinct

G_PROCESSUS.G_PrsName,S_LOG.G_PrsID,

Substring(convert (varchar(1500),S_AnoDescription), patindex('%@ModuleName%',S_ANOMALY.S_AnoDescription) ,50 ),

S_ANOMALY.S_AnoID --,S_ANOMALY.S_LogID

from S_ANOMALY, S_LOG, G_PROCESSUS

where S_AnoCode in (506, 5003) AND

S_ANOMALY.S_LogID = S_LOG.S_LogID

AND G_PROCESSUS.G_PrsID= S_LOG.G_PrsID




Visualisation des Logins Externes :

SELECT  Server = s.srvname,

            Login = l.name,

            Externlogin = a.object_cinfo

        FROM    master.dbo.sysattributes a,

            master.dbo.sysservers s,

            master.dbo.syslogins l

        WHERE   a.class = 9 AND

            a.object_type = "EL" AND

            a.object_info1 = s.srvid AND

--          s.srvname LIKE @server AND

            a.object = l.suid