Bueno, como muchos (o casi todos) los que trabajamos en el rubro informatico me he encontrado incontables veces en la dificil e incomoda situacion de contestar la simple pregunta de: "A que te dedicas?".
Aquel que lea esto y trabaje en sistemas entendera a que me refiero.
Y es que la concepcion de cualquier persona que no tiene contacto con este ambiente es que alguien que trabaja en sistemas "arregla computadoras" o "sabe de computacion" como he escuchado muchas veces.
Y mas alla de lo que el comun de la gente puede especular, trabajar en sistemas NO es sinonimo de saber arreglar una computadora, mas alla de que uno puede estar familiarizado con los componentes de la maquina hay mucha gente que trabaja en distintas posiciones relacionadas a sistemas que no tiene la mas minima idea de como reparar una PC o instalar Windows.
Es por eso que me dedico a estas lineas para clarificar un poco esto y de paso servir para aclarar a mis propios conocidos que es "a lo que me dedico", ya que explicar que es un administrador de bases de datos (o DBA por sus siglas en ingles) a alguien que no trabaja en sistemas es muy dificil, ya que es algo que NO es TANGIBLE.
PRIMERO LO PRIMERO
Primero que nada rompamos el mito general, aquel que estudia sistemas NO estudia como arreglar una computadora, es mas en la mayoria de las carreras de Licenciatura o Ingenieria en sistemas jamas se toca el tema de reparación de PC.
Para reparar una computadora, en general se hace un curso de Técnico reparador de PC o de Tècnico en computación (muchos colegios secundarios los tienen hoy en dia).
Para ponerlo simple un técnico reparador de PC se asemeja a lo que es un mecánico de autos y cumplen a grandes rasgos las mismas funciones.
Tanto el mecanico como el técnico de PC reparan, arman, tunean, customizan y asesoran acerca de FIERROS, es por eso que en la computacion al técnico reparador se lo suele tildar de fierrero muchas veces entre la gente del ambiente, porque es alguien que disfruta y le gusta trabajar con la parte relacionada al hardware y componentes de computadora.
Pero bueno, eso es solo una parte del mundo de la computacion o del mundo de sistemas, es la parte relacionada al hardware y a lo TANGIBLE.
Pero sin embargo hay todo un mundo aparte que se relaciona con lo INTANGIBLE de la computacion o de sistemas. Y eso es donde se enfocan las carreras universitarias de Analista, Licenciatura e Ingenieria de Sistemas.
LO INTANGIBLE
Aca es donde se complica mas explicar que es lo que uno hace. Cuando algo es intangible, donde no es claro a la vista es muy dificil de explicarlo.
Hay una serie de trabajos que se desprenden de las carreras de sistemas como puede ser:
Programadores o Administradores de bases de datos, de plataforma, de Storage o bien
Gerentes y Lideres de Proyectos, Analistas funcionales, Analistas tecnicos, diseñadores de sistemas y la lista podria seguir...
Para hacer una analogía podriamos decir que estudiar sistemas es muy parecido a estudiar medicina.
En la carrera de medicina uno se recibe de Medico Clinico y luego se dedica a especializarse en alguna rama particular de la medicina (traumatologia, obstetricia, ginecologia, etc).
El Licenciado/Ingeniero en sistemas es muy parecido a esto, ya que una vez recibido (a veces desde antes de recibirse inclusive) se especializa en alguna rama en particular de la informatica como las que enumeramos arriba.
El medico trata pacientes, el que trabaja en sistemas trata con sistemas.
Un sistema de informacion puede ser muchas cosas, puede ser un programa en particular, por ejemplo el programa que se usa para la contabilidad, el programa que se usa para liquidar un sueldo. O bien tambien pueden ser cosas mas "abstractas" un sistema tambien es una red, un centro de computos puede ser tratado como un sistema.
El termino es muy amplio.
Y EL DBA ? (administrador de bases de datos)
Bueno, ahora lo que me incumbe a mi.
Que es un DBA, y que significa administrar bases de datos.
Voy a empezar diciendo que todos tratamos con bases de datos a diario sin darnos cuenta en lo absoluto.
Para que se den una idea de como conviven continuamente con bases de datos, cuando van a sacar plata de un cajero, toda esa informacion de su cuenta de banco, su estado de cuenta, sus tarjetas de credito, las operaciones que realizan, todo eso se guarda en una base de datos en algun lado de la empresa BANELCO.
Y para nombrar ejemplos de bases de datos con las que uno convive diariamente tenemos: en internet (que esta plagado), facebook, twitter, wikipedia, la informacion que nos imprimen en los recibos de sueldo, de las boletas de luz, gas , telefono, de sus numeros de celular esta guardado en bases de datos.
Las bases de datos en si son archivos como cualquier archivo de Windows, como un archivo de texto o un archivo de word, nada mas que son archivos de bases de datos y se necesita un programa especifico para manejarlos.
Los administradores de bases de datos son gente especializada para trabajar con estos programas y con datos e informacion de todo tipo y esta capacitada para resolver problemas simples o complejos relacionados a la base de datos que pueden ir desde hacer una copia de respaldo de la informacion hasta resolver problemas complejos o errores que pueden comprometer la disponibilidad de los datos guardados.
Se imaginan que lo ultimo que uno quiere es que por un problema con una base de datos no puedan acceder a su informacion de BANELCO y no puedan sacar su plata.
Para que eso NO pase hay toda una planificacion y mantenimiento de las bases de datos que garantizan que si hay algun problema todo pueda estar funcionando de nuevo lo mas rapido posible sin perder ningun dato en el medio.
Se que es un concepto muy abstracto y complicado de entender, pero espero al menos con esto acercar un poco mas a la compresion de lo que la gente de sistemas hace y especificamente un DBA hace.
Si alguno quiere que explique de manera sencilla alguna de las ramas de la informatica para entender exactamente que hace me lo dicen y se los explico.
Espero no haberlos aburrido.
Saludos!
MaX
Tuesday, November 23, 2010
Wednesday, November 17, 2010
SQL Query - Activity Monitor
Hi All,
Here is quick one.
There is times when you look at the SQL Server activity monitor and you find out that some job triggered by the SQL Agent it's consuming resources or locking processes, and many times when that happen you could have a hard time getting the info from the visual interface from Jobs Activity because you have many jobs running at the same time and you need to identify which is the problematic job.
The problem is that when that happen when you look at the ProgramName column you only will see something like this:
I've used a query to the master..sysprocesses table to return the results in the image above (it's really the same the Activity Monitor GUI does).
Well, as you can see the JobId it's coded and there is no clear reference to which job is generating the problem and as I said, if you have many jobs running at the same time it's a little hard to get the job identified.
Well, for that cases I wrote a query that displays the same information from the Activity Monitor, BUT the decodes the JobName from the JobID and displays the JobName so you can quickly identify the source of your problem.
Here is the code you can execute.
'----------------------------------------------------------------------------------
declare @jobid varchar(60),
@jobname varchar(200),
@stmt nvarchar(100),
@stmt_select varchar(100)
if object_id('tempdb..#procesos')is not null
drop table #procesos;
select spid,
loginame,
blocked,
waittime,
lastwaittype,
db_name(dbid) as dbname,
cpu,
physical_io,
memusage,
open_tran,
status,
cmd,
hostname,
login_time,
last_batch,
program_name,
uid,
hostprocess
into #procesos
from master.dbo.sysprocesses
order by spid
declare cur_procesos cursor for
select substring(program_name,patindex('%0x%',program_name), patindex('%:%',program_name)-patindex('%0x%',program_name))
from #procesos
where program_name like '%Jobstep%'
open cur_procesos
fetch next from cur_procesos into @jobid
while @@fetch_status = 0
begin
set @stmt = N'select @stmt_jobname=name from msdb.dbo.sysjobs where job_id = '+@jobid
exec sp_executesql @stmt,
N'@stmt_jobname varchar(100) OUTPUT',
@stmt_jobname = @jobname OUTPUT;
if (@jobname is not null and @jobname <> '')
begin
set @jobname = 'SQLAgentJob: '+@jobname;
update #procesos
set program_name = @jobname
where current of cur_procesos;
end
fetch next from cur_procesos into @jobid
end
close cur_procesos
deallocate cur_procesos
set @stmt_select = 'select * from #procesos'
exec (@stmt_select)
drop table #procesos
'----------------------------------------------------------------------------------
I hope this help you.
RegardS!
MAX!
Here is quick one.
There is times when you look at the SQL Server activity monitor and you find out that some job triggered by the SQL Agent it's consuming resources or locking processes, and many times when that happen you could have a hard time getting the info from the visual interface from Jobs Activity because you have many jobs running at the same time and you need to identify which is the problematic job.
The problem is that when that happen when you look at the ProgramName column you only will see something like this:
I've used a query to the master..sysprocesses table to return the results in the image above (it's really the same the Activity Monitor GUI does).
Well, as you can see the JobId it's coded and there is no clear reference to which job is generating the problem and as I said, if you have many jobs running at the same time it's a little hard to get the job identified.
Well, for that cases I wrote a query that displays the same information from the Activity Monitor, BUT the decodes the JobName from the JobID and displays the JobName so you can quickly identify the source of your problem.
Here is the code you can execute.
'----------------------------------------------------------------------------------
declare @jobid varchar(60),
@jobname varchar(200),
@stmt nvarchar(100),
@stmt_select varchar(100)
if object_id('tempdb..#procesos')is not null
drop table #procesos;
select spid,
loginame,
blocked,
waittime,
lastwaittype,
db_name(dbid) as dbname,
cpu,
physical_io,
memusage,
open_tran,
status,
cmd,
hostname,
login_time,
last_batch,
program_name,
uid,
hostprocess
into #procesos
from master.dbo.sysprocesses
order by spid
declare cur_procesos cursor for
select substring(program_name,patindex('%0x%',program_name), patindex('%:%',program_name)-patindex('%0x%',program_name))
from #procesos
where program_name like '%Jobstep%'
open cur_procesos
fetch next from cur_procesos into @jobid
while @@fetch_status = 0
begin
set @stmt = N'select @stmt_jobname=name from msdb.dbo.sysjobs where job_id = '+@jobid
exec sp_executesql @stmt,
N'@stmt_jobname varchar(100) OUTPUT',
@stmt_jobname = @jobname OUTPUT;
if (@jobname is not null and @jobname <> '')
begin
set @jobname = 'SQLAgentJob: '+@jobname;
update #procesos
set program_name = @jobname
where current of cur_procesos;
end
fetch next from cur_procesos into @jobid
end
close cur_procesos
deallocate cur_procesos
set @stmt_select = 'select * from #procesos'
exec (@stmt_select)
drop table #procesos
'----------------------------------------------------------------------------------
I hope this help you.
RegardS!
MAX!
Tuesday, November 16, 2010
ToolBox - WinSQL an ODBC Based SQL Client
Hi All,
Today I will quickly review one of the Apps from my personal Toolbox.
In this case I would like to talk about WinSQL Client.
WinSQL it's an ODBC based SQL Client which uses any ODBC installed on your machine to connect to the database. This feature makes it very flexible regarding to which database could connect to, since ODBC protocol it's very ANSI and very universal for all kind of databases. So if your DB Engine has an ODBC Driver you can connect to it.
This App has several editions, Trial, Lite, Developer and Professional.
We can download for free the Trial and if we register they will send us the Lite Edition registration number which is for free.
http://www.synametrics.com/SynametricsWebApp/Download.do?ProgName=WinSQL
I actually started to use this one several years ago when I had one big problem, I had to manage to connect to SQL Server and Oracle while I was making work as a consultant. The problem was that not all companies has the proper tools installed in the PC that they let me use (and of course I didn't had administrative rights over them to install anything), so I had to come up with an App which were portable (no installation needed) and could connect to both SQL and Oracle, so I could run my scripts without issues.
This one was the best option available, and fortunetly we have the possibility to download an version without installer (Portable). This is very useful resource to add to your personal toolbox. I always kept this one in my pendrive which I carry everywhere I go (while working of course :P ).
Here is a couple of screens about the Tools. I'm currently using version 4 (it's quite old) but the current one you can download I think it's 8.
I hope this helps you in your work!
Regards!
MAX!
Today I will quickly review one of the Apps from my personal Toolbox.
In this case I would like to talk about WinSQL Client.
WinSQL it's an ODBC based SQL Client which uses any ODBC installed on your machine to connect to the database. This feature makes it very flexible regarding to which database could connect to, since ODBC protocol it's very ANSI and very universal for all kind of databases. So if your DB Engine has an ODBC Driver you can connect to it.
This App has several editions, Trial, Lite, Developer and Professional.
We can download for free the Trial and if we register they will send us the Lite Edition registration number which is for free.
http://www.synametrics.com/SynametricsWebApp/Download.do?ProgName=WinSQL
I actually started to use this one several years ago when I had one big problem, I had to manage to connect to SQL Server and Oracle while I was making work as a consultant. The problem was that not all companies has the proper tools installed in the PC that they let me use (and of course I didn't had administrative rights over them to install anything), so I had to come up with an App which were portable (no installation needed) and could connect to both SQL and Oracle, so I could run my scripts without issues.
This one was the best option available, and fortunetly we have the possibility to download an version without installer (Portable). This is very useful resource to add to your personal toolbox. I always kept this one in my pendrive which I carry everywhere I go (while working of course :P ).
Here is a couple of screens about the Tools. I'm currently using version 4 (it's quite old) but the current one you can download I think it's 8.
I hope this helps you in your work!
Regards!
MAX!
SQL 2000 - The ArithAbort affects execution plan
A couple of weeks ago I was asked to take a look on a Database View that had some serious performances issues, but with some odd behavior.
I will describe the scenario for you here..
- SQL Server 2000 SP4 Engine, Standard Edition (build 2055)
- This is a very complex view with several subqueries in it, many of them in the Where clause in many others as part of the result set.
The application were this was running it's an old version of an application which uses ODBC to connect to the database.
The thing is that same query to that view runned within the application and from Query Analyzer or SSMS gave different behaviors.
In the first case the query seems to hang and never return the results.
In the second case the query returned the results in just 5 secs.
So, I took several approaches to this problem, here I will enumerate everything that I've considered and tested in order to get to a solution.
- I ruled out the possibility of the problem was the query or the view itself seems the query works on from SSMS or Query Analyzer, so it could not be the query syntax by itself.
- I ran the query using the option SET SHOWPLAN_ALL ON, so it throws me the execution plan.
The problem here it's that I needed to emulate the ODBC connection to the DB, so I needed to get a third party tool from my reliable Pendrive (my personal toolbox), a little application totally portable called WINSQL which connects to any database using the ODBC connections from the local machine. So I ran it in both once in SSMS and another run with WINSQL.
The results from both apps I put it in another app from my toolbox WinMerge to compare both plans line by line.
Here was my surprise when I found out that the same query throwed very different results depending if you use SSMS or WINSQL to run it.
So my first question was, why the hell the SQL optimizer is producing two different plans for the exactly same query.
My first thought was, must be the god damn ODBC Driver. So I performed another battery of test making the following changes on the ODBC Drivers:
- Ran the query using the SQL Server Native Cliente Drivers from SQL 2008 SP1.
- Replace the sqlsrv32.dll DLL from MDAC 2.81 SP1 from the one from MDAC 2.81 SP2.
- Installed a third party ODBC Driver using the DataDirect SQL Wired Drivers.
Non of these test produced different results, the execution plan for every of them was the exactly horrible plan from the beginning.
So I start to think that may be some specific configuration from the ODBC could be messing the plan, my first thoughts were the SET ANSI_NULLS and QUOTED_IDENTIFIERS and the PACKETSIZE. So I did these test to probe if I was right:
Ran the query changing in the ODBC Connection String the following parameters:
- SET QUOTED_IDENTIFIERS and ANSINULLS ON
- SET QUOTED_IDENTIFIERS and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS ON and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS OFF and ANSINULLS ON
- All the same of above both specifically set the PACKETSIZE in the connection string to the same value as it's configured on the SQL Server (using sp_configure to know which value was).
Non of the tests worked as expected, everyone throw the exact laussy execution plan.
So finally scratching my head without much more in it, I start to make a deep search on the wave to try to find anything about this kind of behavior.
Finally one blog article describe that some oddities in the execution plan could occur when you have the SET ARITHABORT user option OFF.
And checking I found out that the damn option was automatically set as default from the Query Analyzer or SSMS (it's a default in the user preferences).
But since this is a execution parameter the ODBC does not have any way to pre-set this to be a default, so of course the ODBC was taking the default settings configured on the Database or Instance level of SQL Server which off course was setted in OFF (it's the default).
I made a final test and found that when I explicitly SET ARITHABORT ON on the ODBC query pane and execute it with the query TA DA, magically the query return results in about 5-6 secs and the execution plan it's identical to the produced when run it from SSMS.
According to BOL, the change of the choices of the optimizer to generate the execution plan it's affected by the ARITHABORT when you have Indexed Views or Computed Columns (which I don't have in my database).
So this was the solution, I mean I only had to change the Database ARITHABORT option setting using ALTER DATABASE Statement and everything start to work fine (don't reboot or outage needed).
For a quick fix was good, well quick(?).. It took me the whole day to find this out.
A couple of days later there was something bothering me, because it does not make sense that the ARITHABORT option affected the execution plan when non of the conditions to it was present, so I started to make a deep analysis of the problem.
What I found out it's that the execution plan for this View has an operation on it called RANK, this operation as per it's description on the visual plan, says that creates a computed column to sort the results. TA DA again, that was the reason, the optimizer was creating at runtime a computed column to perform some kind of sorting and therefore triggering the ARITHABORT constraint.
I still have to analyze which part exactly of this gigantic view triggers the RANK operation (I didn't have time to do it yet), but I will.
This is a pretty weird case at least in my experience and make you thing twice when you analyze something like this because many times the optimizer could do things that indirectly affects the behavior or add constraints to the behavior of the resulting execution plan.
Hope this helps to anyone!.
MAX!
I will describe the scenario for you here..
- SQL Server 2000 SP4 Engine, Standard Edition (build 2055)
- This is a very complex view with several subqueries in it, many of them in the Where clause in many others as part of the result set.
The application were this was running it's an old version of an application which uses ODBC to connect to the database.
The thing is that same query to that view runned within the application and from Query Analyzer or SSMS gave different behaviors.
In the first case the query seems to hang and never return the results.
In the second case the query returned the results in just 5 secs.
So, I took several approaches to this problem, here I will enumerate everything that I've considered and tested in order to get to a solution.
- I ruled out the possibility of the problem was the query or the view itself seems the query works on from SSMS or Query Analyzer, so it could not be the query syntax by itself.
- I ran the query using the option SET SHOWPLAN_ALL ON, so it throws me the execution plan.
The problem here it's that I needed to emulate the ODBC connection to the DB, so I needed to get a third party tool from my reliable Pendrive (my personal toolbox), a little application totally portable called WINSQL which connects to any database using the ODBC connections from the local machine. So I ran it in both once in SSMS and another run with WINSQL.
The results from both apps I put it in another app from my toolbox WinMerge to compare both plans line by line.
Here was my surprise when I found out that the same query throwed very different results depending if you use SSMS or WINSQL to run it.
So my first question was, why the hell the SQL optimizer is producing two different plans for the exactly same query.
My first thought was, must be the god damn ODBC Driver. So I performed another battery of test making the following changes on the ODBC Drivers:
- Ran the query using the SQL Server Native Cliente Drivers from SQL 2008 SP1.
- Replace the sqlsrv32.dll DLL from MDAC 2.81 SP1 from the one from MDAC 2.81 SP2.
- Installed a third party ODBC Driver using the DataDirect SQL Wired Drivers.
Non of these test produced different results, the execution plan for every of them was the exactly horrible plan from the beginning.
So I start to think that may be some specific configuration from the ODBC could be messing the plan, my first thoughts were the SET ANSI_NULLS and QUOTED_IDENTIFIERS and the PACKETSIZE. So I did these test to probe if I was right:
Ran the query changing in the ODBC Connection String the following parameters:
- SET QUOTED_IDENTIFIERS and ANSINULLS ON
- SET QUOTED_IDENTIFIERS and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS ON and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS OFF and ANSINULLS ON
- All the same of above both specifically set the PACKETSIZE in the connection string to the same value as it's configured on the SQL Server (using sp_configure to know which value was).
Non of the tests worked as expected, everyone throw the exact laussy execution plan.
So finally scratching my head without much more in it, I start to make a deep search on the wave to try to find anything about this kind of behavior.
Finally one blog article describe that some oddities in the execution plan could occur when you have the SET ARITHABORT user option OFF.
And checking I found out that the damn option was automatically set as default from the Query Analyzer or SSMS (it's a default in the user preferences).
But since this is a execution parameter the ODBC does not have any way to pre-set this to be a default, so of course the ODBC was taking the default settings configured on the Database or Instance level of SQL Server which off course was setted in OFF (it's the default).
I made a final test and found that when I explicitly SET ARITHABORT ON on the ODBC query pane and execute it with the query TA DA, magically the query return results in about 5-6 secs and the execution plan it's identical to the produced when run it from SSMS.
According to BOL, the change of the choices of the optimizer to generate the execution plan it's affected by the ARITHABORT when you have Indexed Views or Computed Columns (which I don't have in my database).
So this was the solution, I mean I only had to change the Database ARITHABORT option setting using ALTER DATABASE Statement and everything start to work fine (don't reboot or outage needed).
For a quick fix was good, well quick(?).. It took me the whole day to find this out.
A couple of days later there was something bothering me, because it does not make sense that the ARITHABORT option affected the execution plan when non of the conditions to it was present, so I started to make a deep analysis of the problem.
What I found out it's that the execution plan for this View has an operation on it called RANK, this operation as per it's description on the visual plan, says that creates a computed column to sort the results. TA DA again, that was the reason, the optimizer was creating at runtime a computed column to perform some kind of sorting and therefore triggering the ARITHABORT constraint.
I still have to analyze which part exactly of this gigantic view triggers the RANK operation (I didn't have time to do it yet), but I will.
This is a pretty weird case at least in my experience and make you thing twice when you analyze something like this because many times the optimizer could do things that indirectly affects the behavior or add constraints to the behavior of the resulting execution plan.
Hope this helps to anyone!.
MAX!
Subscribe to:
Posts (Atom)