Archive

Archive for October, 2011

Gestion des jobs sur sql server agent sans passer par sql server management studio

October 21st, 2011 No comments

Lorsque l’on veut démarrer un job sur sql server agent, il n’est pas forcément nécessaire d’utiliser l’interface sql server management studio. Vous pouvez piloter vos jobs, soit en créant une interface maison (une web part par exemple) ou directement par sql. Je vous propose de vous décrire les étapes les plus utilisées.

1. Récupération de liste des jobs et leurs statuts

Pour récupérer la liste des jobs, je vous conseille de faire appel a la procédure stockée suivante msdb.dbo.sp_help_job . Cette procédure ramène les informations complètes sur les jobs y compris le statut en cours.


On va s’intéresser à 2 champs :

  • job_id : identifiant du job nécessaire pour accéder à l’historique des jobs.
  • Current_execution_status : spécifie le statut en cours du job.

    Définition de la liste des statuts :
    0 = arrêté ou suspendu,

    1 = en cours d’exécution,
    2 = en attente d’un thread,
    3 = réessaie,
    4 = arrêté,
    5 = suspendu,
    6 = en attente de terminaison d’étape,
    7 = exécutions des actions d’achèvement

2. Démarrage et arrêt d’un job

2.1 Démarrage du job

Pour démarrer un Job sans passé par l’interface de sql server agent, il suffit de faire appel à la procédure stockée msdb.dbo.sp_start_job .Cette procédure nécessite en paramètre le nom du job à exécuter. Comme dans l’exemple ci-dessous :

Exec msdb.dbo.sp_start_job
‘My_Job_Name’

2.2 Arrêt du job

Pour stopper un Job sans passé par l’interface de sql server agent, il suffit de faire appel à la procédure stockée msdb.dbo.sp_stop_job . Cette procédure nécessite également en paramètre le nom du job à exécuter. Comme dans l’exemple ci-dessous :

Exec msdb.dbo.sp_stop_job
‘My_Job_Name’

3. Connaître le résultat de l’exécution d’un job

Avec la table sysjobhistory il est possible de savoir si l’exécution d’un job c’est terminé avec succès ou non. Après avoir récupérer au préalable l’ID du job, il vous suffit de récupérer la dernière instance, est lire la colonne run_status pour connaître le résultat de l’exécution.

SELECT [instance_id] ,[step_id],[run_status] FROM [msdb].[dbo].[sysjobhistory] where [job_id]= ‘JobID’
and [instance_id] in
(select
Max([instance_id]) FROM [msdb].[dbo].[sysjobhistory] where [job_id] =
‘JobID’)

Categories: Trucs & astuces Tags:

Créer une table de logs pour Analysis Services

October 21st, 2011 No comments

L’idée est de montrer comment créer une table SQL qui enregistre les logs d’une instance Analysis Services.

Ces logs permettront par la suite d’extraire des informations tel que :

  • Le nombre d’utilisateurs uniques d’un cube
  • Le nombre de sessions uniques

Ces mesures pourront alors être analysée et suivie par date et par heure.

Configurer l’instance Analysis Services

La configuration par défaut :

  • Au niveau du paramètre Log\QueryLog\QueryLogConnectionString et de la colonne Value, cliquez sur l’icône de sélection :

  • Enfin passez le paramètre Log\QueryLog\CreateQueryLogTable à True :

  • Puis validez en cliquant sur le bouton OK.

La table a alors été créé dans la base de données cible précédemment sélectionnée :

Après quelques requêtes sur le cube, la table se peuple ainsi :

SELECT
TOP 1000 [MSOLAP_Database]


,[MSOLAP_ObjectPath]


,[MSOLAP_User]


,[Dataset]


,[StartTime]


,[Duration]


FROM [Audit].[dbo].[OlapQueryLog]

L’option QueryLogSampling

L’option Log\QueryLog\QueryLogSampling permet de spécifier la fréquence de l’échantillon des requêtes remontées dans la table de logs.

Une valeur à 10, signifie qu’une requête sur 10 serait remontée dans la table de logs.

Si vous souhaitez capturer toutes les logs, il vous faudra donc passer cette valeur à 1 :

Bien entendu, une valeur de 1 peut occasionner de la charge sur votre serveur de base de données.

Requêtes types

Récupération d’information sur l’utilisation du cube ces dernières 24 heures :

— Nb d’utilisateurs uniques

SELECT
COUNT
(DISTINCT [MSOLAP_User] )


FROM [dbo].[OlapQueryLog]


WHERE [StartTime] >=
DATEADD(DAY,
1, GETDATE())

— Nb de requêtes executées

SELECT
COUNT(*)


FROM [dbo].[OlapQueryLog]


WHERE [StartTime] >=
DATEADD(DAY,
1, GETDATE())

— Cout des requêtes executées

SELECT
SUM(Duration)


FROM [dbo].[OlapQueryLog]


WHERE [StartTime] >=
DATEADD(DAY,
1, GETDATE())

— Liste des utilisateurs d’une base de données

SELECT MSOLAP_Database, MSOLAP_User, StartTime, Duration,

CAST(CONVERT(varchar(8), StartTime, 112) AS
int) AS
Day

FROM OlapQueryLog

WHERE (MSOLAP_Database =
‘DataWarehouse’)
and [StartTime] >=
DATEADD(DAY,
30, GETDATE())

ORDER
BY StartTime DESC

— Liste des utilisateurs par base de données et par jour

SELECT MSOLAP_Database, MSOLAP_User,
SUM(Duration)
AS Duration,

CAST(CONVERT(varchar(8), StartTime, 112) AS
int) AS Day_FK,
COUNT(*)
AS QueryCount

FROM OlapQueryLog

GROUP
BY MSOLAP_Database, MSOLAP_User,
CAST(CONVERT(varchar(8), StartTime, 112) AS
int)

ORDER
BY Day_FK

Exemple de rapports et d’indicateurs

La table de log, vous permettra ainsi d’alimenter certains indicateurs de votre tableau de bord d’exploitation :

De pouvoir suivre l’évolution du nombre d’utilisateurs consommateurs de vos cubes au quotidien :

Et bien entendu d’en avoir la liste :

Grouby By Concat en Transac SQL

October 21st, 2011 No comments

Quand on doit gérer des relations en Many to Many en SQL, on se retrouve confronté au problème de

“Comment concaténer les valeurs d’une colonne de plusieurs lignes”
ou
“Comment simuler une fonction de concaténation comme agrégation d’un group by”?

Ex :

Une solution efficace et rapide est d’utiliser la clause FOR XML :

SELECT p1.CategoryId,
( SELECT ProductName + ‘,’
FROM Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH(”) ) AS ProductList,COUNT(1) as ProductCount
FROM Products p1
GROUP BY CategoryId ;

Pour une étude détaillée des différentes solutions utlisables en Transac SQL, voici une excellente étude du problème :
http://www.simple-talk.com/content/article.aspx?article=539

CategoryID ProductName
1 P1
1 P2
1 P3
2 P4
2 P5
Categories: Divers Tags: