A day in a DBA life
By Mauro Pichiliani
Here is my entry for the contest. Please, read the following as an entry in a DBA's personal journal. And yes, unfortunately the following facts are based in a true storey.
07:50. My arrival at work. I came 10 minutes before the start of my turn in order to see if I can start ahead some work before my coworkers and my by boss arrive although I did some extra time yesterday. As always, one of the programmers has on its disk two full spread pages of a morning tabloid open. He says hello to me and start to talk about the latest celebrity scandal.
08h:10. Finally the programmer realizes that I was not interested in the details about the celebrity affairs. I sat down on my not so comfortable chair (the department's boss is the only one that have a good leather chair) and log in. I found 25 e-mails marked as not read: 8 are about a discussion regard an user unhappy about the ERP system, 10 are spam, 1 is a greeting about an upcoming birthday of someone that I don't know. The rest of them I cannot understand how the hell they are forwarded to me since they talk about the poor specification of a system that don't even use a database.
09h:05. Writing an e-mail to the infra guys on which I ask for a follow up about the restart problem of the servers. Also querying them about other issues such as: the delayed purchased of more memory to a database server (one week delayed), the purchase of another server to create the second SQL Server cluster node of a major database (one month delayed), the installation of the latest service pack on all the Windows servers (lost track about the delay of this one...) and requesting the firewall authorization to access the www.microsoft.com website that was blocked by mistake for my net user. I remember to send another e-mail for the help desk guys asking to replace my monitor, since it show a horizontal green like across it for the last three weeks. Last time one of the help desk guys came they replaced my optical mouse by an analog one and don't even looked at my monitor.
09h:25. The analysts start to receive phone calls from the users while I go get a coffee. I don't even get mad to find out that today's coffee is basically what left from yesterday warmed up. I go for a tea while I hear the following phrase said by an analyst during a phone talk with some user:
"bla bla bla... the system is working perfectly... bla bla bla... do you want me to fix it using the right or the quick way? Bla bla bla..."
I think to myself: this is going to be a long day. No signal of the boss yet.
09h:50. The analyst talks to me and say that the HR system is extremely low today. The responsibility about this system was upon a programmer that quit last week and there wasn't a person assigned to handle it. This was the start of a great performance problem that took the rest of my day. The analyst said to me that this was a major task and has the biggest priority because if this system is not working on time the HR guys cannot print the papers necessary to send all the employees month payment. A little bit of physiological pressure is in order, since if I cannot solve this on time everyone in the company will receive it's payment with delay and I will be the one to receive the full blame.
10h:15. While I was looking the SQL Server code used by the specific task some analysts join a gang to lower down the air conditioning level and the interns, programmers and other department's member gather to battle for the increase of the air conditioning level. I can hear the sound of knuckles snapping. Meanwhile I try to focus on a stored procedure's code that contains strange variable names such as SATURN1 to store an employee code, PLUTO69 to store the number days worked...
11h:45. This programmer was really something. He has the ability to use everything in the database in the wrong way. For instance, he used cursors to navigate in a temporary table that has 100.000 rows and no index. So I start rewriting it to employ many SELECT statements. While looking for the stored procedure I found more interesting things that can fit perfectly in a horror show: instead of triggers with many levels of recursion! And all of this copped with queries with SELECT *, many updates without a WHERE clause and on top of that the following compled select that store its data in a temporary table:
TIT_ID,dev_cpf CPF,MAX(substring(DEF_FONE,1,4))DDD,MAX(substring(DEF_FONE,5,8))FONE,SALDO, ATRASO, TERMINAL, SEGMENTO, PARCELADO, UF,replace(replace(substring(acn_descricao,1,2),'Re','19'),'-','') COD_ATEND,convert(varchar(30),dea_data,103)AS DTE,convert(varchar(30),DATEPART(HH,DEA_DATA))+':'+convert(varchar(30),DATEPART(mi,DEA_DATA))+':'+convert(varchar(30),DATEPART(ss,DEA_DATA)) AS TME,USU_CODIGO_ACESSO AS AGENT,ISNULL(epo_codigo,'0') PESQ_INADIMP,replace(substring(DEA_HISTORICO,charindex('- Agendado para',DEA_HISTORICO),26),'- Agendado para','')DT_AGENDA,TPA_DATA_DEVOLUCAO DT_BAIXA
from tab_devedor_acionamento (nolock)
inner join tab_devedor (nolock) on dea_devedor=dev_id
inner join tab_devedor_telefone (nolock) on def_devedor=dev_id
inner join tab_acionamento (nolock) on dea_acionamento_id=acn_id
inner join tab_usuario (nolock) on dea_usuario=usu_id
inner join #BRTC(nolock) on tit_devedor=dea_devedor
LEFT JOIN TAB_TITULO_PARCELA (NOLOCK)ON TIT_ID=TPA_TITULO AND TPA_STATUS='D'
inner join tab_carteira (nolock) on car_id=tit_carteira AND CAR_DESCRICAO<>'TESTE'
left join TAB_ENQUETE_RESPOSTA (nolock) on ere_devedor=dev_id AND CONVERT(VARCHAR(30),DEA_DATA,103)=CONVERT(VARCHAR(30),ERE_DATA,103)
left join TAB_ENQUETE_RESPOSTA_ITEM (nolock) on ERI_ENQUETE_RESPOSTA=ERE_ID
left join TAB_ENQUETE_PERGUNTA_OPCAO (nolock) on epo_sequencia=ERI_ENQUETE_PERGUNTA_OPCAO
DEA_DATA BETWEEN '2009-02-10 07:00:00.000' AND '2009-02-23 22:00:00.000'
and acn_grupo_acionamento IN (1014,1016)
AND TIT_CARTEIRA = 23908 --fixa
group by SALDO, ATRASO,TIT_ID,dea_id,dev_cpf , dea_data,acn_descricao,USU_CODIGO_ACESSO,usu_nome,ACN_DESCRICAO,TERMINAL, SEGMENTO, PARCELADO, UF,epo_codigo,USU_CODIGO_ACESSO,replace(substring(DEA_HISTORICO,charindex('- Agendado para',DEA_HISTORICO),26),'- Agendado para',''),TPA_DATA_DEVOLUCAO --,epo_codigo--,COD_ATEND, DTE, TME,AGENT
order by dev_cpf,tme
I used the great on-line SQL formater (http://www.dpriver.com/pp/sqlformat.htm) to simplify this query. Needless to say, I send a few hours trying to understand it and to break it into simpler SELECT statements. By the way, no launch time for me while I did not fix the HR performance problem. During the study of the query I noticed the triumphal boss entrance. I could hear the sound made by multiple ALT+TAB keystrokes and small talks being stopped abruptly. As background noise I could almost hear the Star Wars imperial march while the boss walks toward its desk at the end of the room where he can see what is going on in the monitor of everyone in the department.
14:27. After understanding the query I did a simple test case to ensure that any modification on it do not produce different results. The query received some parameters for the columns (such as TIT_CARTEIRA and CAR_ID) but I was able to use a technique called dynamic search conditions to isolate then and be more fast. For more information about dynamic search conditions check this link: http://www.sommarskog.se/dyn-search-2008.htm.
16:15. I did my best to break the big and complex query and to optimize it with indexes and other techniques. In this exactly time the internet is down. Luckily for me, I did have a local copy of the code I was working on. But other analysts and programmers start a game of poker, play the harmonica and I could swear I hear a guitar riff nearby.
17:20. After more work in the stored procedure I was stuck in a specify query that seems OK but took more time than it should. I tried many ways to optimized it but I still could not do it. A quick look at the execution plan show that some parallelism has been done by the optimizer but some of the cores of the Intel 16 cores processor were not being used during the query execution. I recall a great new approach to use parallelism via a .NET assembly that I studied a few weeks ago when I was writing an article to compare the alternatives to explore parallelism in SQL Server. The following link show the assembly I used and that was proposed by Alan Kaplan in an article called "Asynchronous T-SQL Execution Without Service Broker": http://www.codeproject.com/KB/DATABASE/ASYNCHRONOUSTSQL.ASPX
18:30. Internet's back. I adapted the long running query in 5 threads in order to split the load. What I did was to separate each filter in the WHERE clause with a simple select statement. The result of the separated queries stored its contents in a temporary table. For instance, the following piece of the select statement:
where DEA_DATA BETWEEN '2009-02-10 07:00:00.000' AND '2009-02-23 22:00:00.000'
and acn_grupo_acionamento IN (1014,1016)
AND TIT_CARTEIRA = 23908
Was broken down to 5 threads something like this:
-- Thread 1
select ... where DEA_DATA BETWEEN '2009-02-10 07:00:00.000' AND '2009-02-23 22:00:00.000'
-- Thread 2
select... where acn_grupo_acionamento IN (1014,1016)
-- Thread 3
select ...where tit_cliente=60
-- Thread 4
select ...where TIT_CARTEIRA = 23908
-- Thread 5
select ...where CAR_ID=23930
Then I executed these 5 queries in parallel with Alan Kaplan's assembly and stored its results in a temporary table. The rest of the processing (aggregations, joins, etc) was done directly in the temporary table. I did notice that with this approach every core of the server now was busy and the query runs much, much more faster. In the end, the results vary according to the values of the parameters, but in some cases the speedup go up to 5x.
To finish up, I made some tests in the development environment with the analyst and a programmer. Then we implemented it on the production environment while our boss was telling that every extra hour will not be paid in money and must be stored in a hour bank that will be dealt with later on. When we tested the HR application with the user we received a simple answer:
'It was about time that the IT departments solved this one. Everyone was expecting it a few hours ago! Why did it so long guys? I mean, you probably just have to push some buttons or change some parameters to make that specific function in the application runs faster, isn't it? I bet any of our interns could have done it in 5 minutes'.