June 19, 2013 at 5:28 am
Sai Viswanath (6/18/2013)
Hi Gail,Am the so called consultant they have hired. My background is - Data Modelling, and they have picked me as Sr. DBA for this project. Fixing of queries for performance is no way possible, reason am not getting a chance to view them while generating the report. So, I had to do something on the db.
Regards,
Sai Viswanath
You need to tell them that you can't enhance performance without access to the queries.
Partitioning has never been sold as a performance enhancing solution and without fixing the queries there's no guarantee that it won't make the situation worse. If you want to bet your job on that, it's your choice. But you should be up front with them that this is not your area of expertise so they can't blame you for all woes that come after you're done with this project.
As far as the poster who said you won't need DBCC commands, please ignore him. DBCC commands are a vital part of any DBA's toolkit and most of them should be used frequently for preventative maintenance.
June 19, 2013 at 5:57 am
"Am the so called consultant they have hired. My background is - Data Modelling, and they have picked me as Sr. DBA for this project. Fixing of queries for performance is no way possible, reason am not getting a chance to view them while generating the report. So, I had to do something on the db."
You can't see the queries, but you can make major structural changes to the database? Wow, that's unusual.
Please listen to Brandie and Gail, they are both entirely right in what they are saying. In this situation, as a data modeller, and from the sounds of it not at all comfortable with performance tuning on this system your first question might be "Why have we got a snakepit of ad-hoccery hitting our live system for reporting". How many of these reports need to be up to the minute? Few to none I bet.
I bet the previous day's x, y, z reports are hammering the system, as are the monthly and quarterly roll-ups.
Use your data modelling skills to implement a reporting solution refreshed nightly, take the hit off the live system and you can have their reporting flying. And probably a damned sight more accurate than the current stuff. There, you can use the skills you DO have, rather than implement something you do not understand, a potential disaster in the making that could get you sued.
Also - if you've not updated statistics since the upgrade, do that. Not doing so is a performance hit right there. Also, if they haven't already, implement an index maintenance
proccess like Michelle Ufford's - could be a huge performance gain right there. Have a look at Brent Ozar's sp_Blitz and sp_Blitz index. Potential performance gains right there.
Partitioning - nah. Read the links you've been given, summarize for your client, explain the options. If you lose 9 days work it bites, but less so than trashing their system by implementing a poorly understood process that carries risks with it
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 19, 2013 at 6:33 am
I agree with Andrew, as well as someof the other points made by Gail and Brandie.
Partitioning should come after some of the other modeling efforts.
Part of the modeling usually involves some understanding of what / how we want to query the data.
We partitioned by Date, which is a pretty common use.
I'm sure there are other new features not in use.
Understanding how they can enhance performance or solve / simplify a problem leads to a better implementation.
Just because it's available, doesn't mean you need it. And sometimes can lead to bigger issues.
June 19, 2013 at 7:11 am
From what you've said, you don't need partitioning, you need a redesign of both the database and the process. Partitioning is hugely complex and not something to be implemented in a rush. I've used it and it doesn't really give you a boost in performance except in making it easier to move data to archive tables. If you only query the non-archive tables you'll get the improvement in query performance because there's less data to search, not because of partitioning.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 19, 2013 at 8:47 am
Stefan Krzywicki (6/19/2013)
From what you've said, you don't need partitioning, you need a redesign of both the database and the process. Partitioning is hugely complex and not something to be implemented in a rush. I've used it and it doesn't really give you a boost in performance except in making it easier to move data to archive tables. If you only query the non-archive tables you'll get the improvement in query performance because there's less data to search, not because of partitioning.
Seconded. Been there, learned the lesson.
June 19, 2013 at 8:57 am
Hi All,
Thanks for the comments, and here's the code that I could get from the SQL Server Profiler when I worked with a report. The tables its referring donot have any primary keys, and NonClustered indexes are defined on almost all the columns, and one or group of columns will be added for Unique, Clustered Index.
1. usp_dbExists 'BACKPD21_Work'
2. usp_cleandb 'BACKPD21_Work','1'
3. usp_droptable 'BACKPD21_Work','GEO_SELECT','1'
4. CREATE TABLE "GEO_SELECT"("ZIPSN" int)
5. CREATE UNIQUE INDEX "GeoSN" ON "dbo"."GEO_SELECT" ("ZIPSN")
6. usp_droptable 'BACKPD21_Work','TC_LocalRatesAgeSex','1'
7. usp_dropview 'BACKPD21_Work','TC_LocalRatesAgeSex','1'
8. Create View [TC_LocalRatesAgeSex] As Select * From DLMESQLCLUSPROD.STD13.dbo.LocalRatesAgeSex
9. usp_droptable 'BACKPD21_Work','TC_OPERate','1'
10. usp_dropview 'BACKPD21_Work','TC_OPERate','1'
11. Create View [TC_OPERate] As Select * From DLMESQLCLUSPROD.CORE.dbo.OPERate
12. usp_droptable 'BACKPD21_Work','TC_ZIPICEPopulationYear','1'
13. usp_dropview 'BACKPD21_Work','TC_ZIPICEPopulationYear','1'
14. Create View [TC_ZIPICEPopulationYear] As Select * From DLMESQLCLUSPROD.STD13.dbo.ZIPICEPopulationYear
15. usp_droptable 'BACKPD21_Work','QZ_OPE0073_1','1'
16. usp_dropview 'BACKPD21_Work','QZ_OPE0073_1','1'
17. Create View [QZ_OPE0073_1] as Select TC_ZIPICEPopulationYear.YearID, TC_ZIPICEPopulationYear.DemoYear, TC_ZIPICEPopulationYear.FIPSCountyCode, TC_ZIPICEPopulationYear.AgesexCode, TC_OPERate.ProcNum, TC_OPERate.PayorCode, TC_OPERate.SiteServCode, TC_LocalRatesAgeSex.LocalAdjAgeSexCode, Sum(TC_ZIPICEPopulationYear.Pop*TC_OPERate.Rate) As [Procs1], Sum(TC_ZIPICEPopulationYear.BasePop*TC_OPERate.Rate) As [BaseProcs1] From ((GEO_SELECT INNER JOIN TC_ZIPICEPopulationYear ON GEO_SELECT.ZIPSN=TC_ZIPICEPopulationYear.ZIPSN) INNER JOIN TC_LocalRatesAgeSex ON TC_ZIPICEPopulationYear.AgeSexCode=TC_LocalRatesAgeSex.AgeSexCode) INNER JOIN TC_OPERate ON (TC_ZIPICEPopulationYear.AgeSexCode=TC_OPERate.AgeSexCode) AND (TC_ZIPICEPopulationYear.PayorCode=TC_OPERate.PayorCode) WHERE (((TC_LocalRatesAgeSex.PediatricAgeGroup)='Y')) GROUP BY TC_ZIPICEPopulationYear.YearID, TC_ZIPICEPopulationYear.DemoYear, TC_ZIPICEPopulationYear.FIPSCountyCode, TC_OPERate.ProcNum, TC_OPERate.PayorCode, TC_OPERate.SiteServCode, TC_LocalRatesAgeSex.LocalAdjAgeSexCode, TC_ZIPICEPopulationYear.AgesexCode
18. usp_droptable 'BACKPD21_Work','TC_CountyOPEVariation','1'
19. usp_dropview 'BACKPD21_Work','TC_CountyOPEVariation','1'
20. Create View [TC_CountyOPEVariation] As Select * From DLMESQLCLUSPROD.STD13.dbo.CountyOPEVariation
21. usp_droptable 'BACKPD21_Work','QZ_OPE0073_2','1'
22. usp_dropview 'BACKPD21_Work','QZ_OPE0073_2','1'
23. Create View [QZ_OPE0073_2] as Select QZ_OPE0073_1.YearID, QZ_OPE0073_1.DemoYear, QZ_OPE0073_1.ProcNum, QZ_OPE0073_1.PayorCode, QZ_OPE0073_1.AgesexCode, QZ_OPE0073_1.SiteServCode, TC_CountyOPEVariation.Attenuate, (QZ_OPE0073_1.Procs1*TC_CountyOPEVariation.RATIO) As [Procs2], (QZ_OPE0073_1.BaseProcs1*TC_CountyOPEVariation.RATIO) As [BaseProcs2] From QZ_OPE0073_1 INNER JOIN TC_CountyOPEVariation ON (QZ_OPE0073_1.LocalAdjAgeSexCode=TC_CountyOPEVariation.LocalAdjAgeSexCode) AND (QZ_OPE0073_1.FIPSCountyCode=TC_CountyOPEVariation.FIPSCountyCode) AND (QZ_OPE0073_1.SiteServCode=TC_CountyOPEVariation.SiteServCode) AND (QZ_OPE0073_1.ProcNum=TC_CountyOPEVariation.ProcNum)
24. usp_droptable 'BACKPD21_Work','TC_OPEAdjustmentYear','1'
25. usp_dropview 'BACKPD21_Work','TC_OPEAdjustmentYear','1'
26. Create View [TC_OPEAdjustmentYear] As Select * From DLMESQLCLUSPROD.STD13.dbo.OPEAdjustmentYear
27. usp_droptable 'BACKPD21_Work','QZ_OPE0073_3','1'
28. usp_dropview 'BACKPD21_Work','QZ_OPE0073_3','1'
29. Create View [QZ_OPE0073_3] as Select QZ_OPE0073_2.DemoYear, QZ_OPE0073_2.ProcNum, QZ_OPE0073_2.Agesexcode, Sum(QZ_OPE0073_2.Procs2*TC_OPEAdjustmentYear.ReformAdj) As [AdjProcs3], Sum(QZ_OPE0073_2.Procs2*(1+((TC_OPEAdjustmentYear.ReformTrendAdj-1)*QZ_OPE0073_2.Attenuate))) As [AdjTrendProcs3], Sum(QZ_OPE0073_2.BaseProcs2) As [BaseProcs3] From QZ_OPE0073_2 INNER JOIN TC_OPEAdjustmentYear ON (QZ_OPE0073_2.PayorCode=TC_OPEAdjustmentYear.PayorCode) AND (QZ_OPE0073_2.ProcNum=TC_OPEAdjustmentYear.ProcNum) AND (QZ_OPE0073_2.SiteServCode=TC_OPEAdjustmentYear.SiteServCode) AND (QZ_OPE0073_2.YearID=TC_OPEAdjustmentYear.YearID) GROUP BY QZ_OPE0073_2.DemoYear, QZ_OPE0073_2.ProcNum, QZ_OPE0073_2.Agesexcode
30. usp_droptable 'BACKPD21_Work','TC_OPEPediatricAgeSex','1'
31. usp_dropview 'BACKPD21_Work','TC_OPEPediatricAgeSex','1'
32. Create View [TC_OPEPediatricAgeSex] As Select * From DLMESQLCLUSPROD.STD13.dbo.OPEPediatricAgeSex
33. usp_droptable 'BACKPD21_Work','TC_OPEPediatricAdjustments','1'
34. usp_dropview 'BACKPD21_Work','TC_OPEPediatricAdjustments','1'
35. Create View [TC_OPEPediatricAdjustments] As Select * From DLMESQLCLUSPROD.Core.dbo.OPEPediatricAdjustments
36. usp_droptable 'BACKPD21_Work','QZ_OPE0073_4','1'
37. usp_dropview 'BACKPD21_Work','QZ_OPE0073_4','1'
38. Create View [QZ_OPE0073_4] as Select QZ_OPE0073_3.DemoYear, QZ_OPE0073_3.ProcNum, TC_OPEPediatricAgeSex.PediatricSex, TC_OPEPediatricAgeSex.PediatricAge, Sum(QZ_OPE0073_3.AdjProcs3*TC_OPEPediatricAdjustments.AgeSexPercent) As [AdjProc], Sum(QZ_OPE0073_3.AdjTrendProcs3*TC_OPEPediatricAdjustments.AgeSexPercent) As [AdjTrendProc], Sum(QZ_OPE0073_3.BaseProcs3*TC_OPEPediatricAdjustments.AgeSexPercent) As [BaseProc] From QZ_OPE0073_3 INNER JOIN (TC_OPEPediatricAdjustments INNER JOIN TC_OPEPediatricAgeSex ON TC_OPEPediatricAdjustments.PediatricAgeSexCode=TC_OPEPediatricAgeSex.PediatricAgeSexCode) ON (QZ_OPE0073_3.ProcNum=TC_OPEPediatricAdjustments.ProcNum) AND (QZ_OPE0073_3.Agesexcode=TC_OPEPediatricAdjustments.AgeSexCode) GROUP BY QZ_OPE0073_3.DemoYear, QZ_OPE0073_3.ProcNum, TC_OPEPediatricAgeSex.PediatricSex, TC_OPEPediatricAgeSex.PediatricAge
39. usp_droptable 'BACKPD21_Work','TC_ClinicalServiceCategory','1'
40. usp_dropview 'BACKPD21_Work','TC_ClinicalServiceCategory','1'
41. Create View [TC_ClinicalServiceCategory] As Select * From DLMESQLCLUSPROD.Core.dbo.ClinicalServiceCategory
42. usp_droptable 'BACKPD21_Work','TC_OPEProcedureGroup','1'
43. usp_dropview 'BACKPD21_Work','TC_OPEProcedureGroup','1'
44. Create View [TC_OPEProcedureGroup] As Select * From DLMESQLCLUSPROD.Core.dbo.OPEProcedureGroup
45. usp_droptable 'BACKPD21_Work','TC_AmbulatoryTechnicalGroup','1'
46. usp_dropview 'BACKPD21_Work','TC_AmbulatoryTechnicalGroup','1'
47. Create View [TC_AmbulatoryTechnicalGroup] As Select * From DLMESQLCLUSPROD.Core.dbo.AmbulatoryTechnicalGroup
48. usp_droptable 'BACKPD21_Work','QZ_OPE0073','1'
49. usp_dropview 'BACKPD21_Work','QZ_OPE0073','1'
50. Create View [QZ_OPE0073] as Select TC_ClinicalServiceCategory.ClinicalSrvcCat As [ClinicalServiceCategory], TC_AmbulatoryTechnicalGroup.AmbTechGrp As [AmbulatoryTechnicalGroup], TC_OPEProcedureGroup.ProcedureGroup, QZ_OPE0073_4.PediatricSex As [Sex], QZ_OPE0073_4.PediatricAge As [AgeGroup], QZ_OPE0073_4.DemoYear As [Year], QZ_OPE0073_4.AdjProc As [AdjProcs], QZ_OPE0073_4.AdjTrendProc As [AdjTrendProcs], QZ_OPE0073_4.BaseProc As [BaseProcs] From (TC_AmbulatoryTechnicalGroup INNER JOIN (QZ_OPE0073_4 INNER JOIN TC_OPEProcedureGroup ON QZ_OPE0073_4.ProcNum=TC_OPEProcedureGroup.ProcNum) ON TC_AmbulatoryTechnicalGroup.AmbTechGrpCode=TC_OPEProcedureGroup.AmbTechGrpCode) INNER JOIN TC_ClinicalServiceCategory ON TC_OPEProcedureGroup.ClinicalSrvcCatCode=TC_ClinicalServiceCategory.ClinicalSrvcCatCode
51. usp_trncateshrink 'BACKPD21_Work','1'
52. SELECT Config, nValue FROM MSysConf
53. SELECT "ClinicalServiceCategory" ,"AmbulatoryTechnicalGroup" ,"ProcedureGroup" ,"Sex" ,"AgeGroup" ,"Year" ,"AdjProcs" ,"AdjTrendProcs" ,"BaseProcs" FROM "dbo"."QZ_OPE0073"
54. SELECT "ClinicalServiceCategory" ,"AmbulatoryTechnicalGroup" ,"ProcedureGroup" ,"Sex" ,"AgeGroup" ,"Year" ,"AdjProcs" ,"AdjTrendProcs" ,"BaseProcs" FROM "dbo"."QZ_OPE0073"
55. SELECT Config, nValue FROM MSysConf
56. SELECT "ClinicalServiceCategory" ,"AmbulatoryTechnicalGroup" ,"ProcedureGroup" ,"Sex" ,"AgeGroup" ,"Year" ,"AdjProcs" ,"AdjTrendProcs" ,"BaseProcs" FROM "dbo"."QZ_OPE0073"
57. SELECT "ClinicalServiceCategory" ,"AmbulatoryTechnicalGroup" ,"ProcedureGroup" ,"Sex" ,"AgeGroup" ,"Year" ,"AdjProcs" ,"AdjTrendProcs" ,"BaseProcs" FROM "dbo"."QZ_OPE0073"
Regards,
Sai Viswanath
June 19, 2013 at 9:02 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 9:55 am
I especially (dis)like the
11. Create View [TC_OPERate] As Select * From DLMESQLCLUSPROD.CORE.dbo.OPERate.
There are a few more of these.
June 19, 2013 at 11:16 am
Hi Jan,
Even I had raised this question with the development team, but they could not provide me satisfactory answer. The tables are having huge data, and that's one of the issue. Also, could you point out which part of the output has got incorrect structure.
Regards,
Sai Viswanath
June 19, 2013 at 11:21 am
Sai Viswanath (6/19/2013)
Also, could you point out which part of the output has got incorrect structure.
The SELECT *, the fact that it's across a linked server, the lack of a where clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 1:09 pm
I don't even think there is a linked server situation here, Gail. Not if you look at all those CREATE VIEW statements, they all select from the same server, which makes me believe that those are actually local. Anyhow, that's an additional issue which only the OP can clarify.
What I also see is statements 53 to 57, which each do the same thing, again a SELECT <somecolumns> without a WHERE clause. Sai, you are saying there's lots of data in those tables. Not having a WHERE clause will certainly cause your performance to take a huge knock. You need to help the server to work on as little data as possible, columnwise and rowwise. So, get rid of the SELECT *, and add WHERE clauses.
June 19, 2013 at 2:38 pm
Sai,
To state the obvious: when you talk to them you'll want to leave out the part where you posted the queries they didn't want you to know about to the Internet asking for advice.
-Jim
June 19, 2013 at 3:14 pm
Sai Viswanath (6/19/2013)
Hi All,Thanks for the comments, and here's the code that I could get from the SQL Server Profiler when I worked with a report. The tables its referring donot have any primary keys, and NonClustered indexes are defined on almost all the columns, and one or group of columns will be added for Unique, Clustered Index.
1. usp_dbExists 'BACKPD21_Work'
2. usp_cleandb 'BACKPD21_Work','1'
...
<Raging insanity snipped>
...
,"ProcedureGroup" ,"Sex" ,"AgeGroup" ,"Year" ,"AdjProcs" ,"AdjTrendProcs" ,"BaseProcs" FROM "dbo"."QZ_OPE0073"
Regards,
Sai Viswanath
That made my eyes bleed. I have no idea what in the name of Babbge they thought they were doing there. Man, my heart goes out to you. That's beyond ugly.
Sorry to harp on - partitioning is not going to help that _d up mess.
I am one of those who normally wants to slap anyone who has a first response of "We'll just re-write it" - generally I'm with Joel http://www.joelonsoftware.com/articles/fog0000000069.html
This looks to me like a special case.
It's badly thought out, untunable, unmaintainable and it's not even legacy code, it's liability code. THere are people like Gail on here, and others who are world class tuners (I'm not). I'd be surprised if they'd try and tune that rather than scrapping it (setting myself up for a kicking here I suppose).
What you need to do is identify precisely what business need this is supposed to address. This does not mean "We run it every morning". Who looks at it, what are the key facts they take from it, what actions are taken based on it's information. Identify if it's Cargo Cult Management - they go through the motions of getting the report, ignoring it/reading i then ignoring it/reading it emailing someone or phoning them then ignoring it. If the report never produces action it's less than useless. It soaks up activity and provides no value.
Bluntly, I've very rarely seen a report where the logic is as non-existant as that which is accurate. You need to find out what the business needs are of the garbage that's being thrown at your Line of Business System. If it's not being actively used to drive business decisions, it's waste. Get rid. Assess what outputs are required, prioritise and build an effective reporting infrastructure - preferably using your data modelling skills to build an optimised and accurate reporting datawarehouse (I do go on a bit, don't I)?
That's not a 10 day job. If your client won't accept that - go for the quick wins I suggested earlier, you can probably get that in in 10 days, show an improvement and escape with your reputation intact.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 19, 2013 at 7:21 pm
Kingston Dhasian (6/19/2013)
Sai Viswanath (6/19/2013)
Hi All,One more doubt on partitioning.
- Do we need to have a primary key in the table which needs to be partitioned?
- Can I use a composite key index to work with partitioning? Ex - In my table I had columns from Col1 .. Col10, and I had created an Unique, Clustered Index by combining - Col1,Col2,Col3. Now, can I go ahead in partitioning my table.
Regards,
Sai Viswanath
Please read through the article[/url] that Gail has suggested
There are a few more resources on partitioning here [/url]
Partitioning is not some magic wand to improve performance and you should never expect miracles
You will have to analyze a lot of things before you finalize some strategy to improve performance
As you are a Sr.DBA, you should tell the client the same with reasons failing which you might be in trouble when the 10 days get over.
Kingston - thanks for posting these links... which I've now bookmarked.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 20, 2013 at 2:10 am
WayneS (6/19/2013)
Kingston Dhasian (6/19/2013)
Sai Viswanath (6/19/2013)
Hi All,One more doubt on partitioning.
- Do we need to have a primary key in the table which needs to be partitioned?
- Can I use a composite key index to work with partitioning? Ex - In my table I had columns from Col1 .. Col10, and I had created an Unique, Clustered Index by combining - Col1,Col2,Col3. Now, can I go ahead in partitioning my table.
Regards,
Sai Viswanath
Please read through the article[/url] that Gail has suggested
There are a few more resources on partitioning here [/url]
Partitioning is not some magic wand to improve performance and you should never expect miracles
You will have to analyze a lot of things before you finalize some strategy to improve performance
As you are a Sr.DBA, you should tell the client the same with reasons failing which you might be in trouble when the 10 days get over.
Kingston - thanks for posting these links... which I've now bookmarked.
🙂
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply