March 11, 2010 at 8:20 am
You guys are great and I really appreciate your insight. OK, so now that I have vented my frustrations let's get down to work. I am trying to figure out the order of operations here. I need to ultimately get control of the data platform so that is becomes a loosely coupled system from the application. One main reason for this is so I can begin on architecture changes on the back end while still serving the application. This brings in the Database-As-API methodology where you need to treat the db platform like any another system that exposes contract interfaces where any allowed service and take advantage of. The only SQL Server construct to handle this is the stored procedure. Now I can begin to query out the dm_exec_query_stats metadata view for queries against a given object and start putting together a query and column list. The other big thing however is that I need to get the query execution from the app down substantially. As I stated before, we generate nearly 200 million queries every day against our platform and most queries take on the ORM form of 'select * from view where id = @id'. What do I need to do to get this execution count down? I know without having a clear understanding of the architecture this may be difficult, but has anyone seen similiar problems and how did they correct it from an application standpoint? Once I have a listing of the queries I then will need to meet with members of development to get there understanding on how the result set is being used. I found this great quote which I have hanging over my desk
"Every time a select * enters production code, God kills a kitten".
I need to truly understand what is needed from the application standpoint. Once I have a full understanding of what is necessary for the product development group I can begin to take pieces and build exposed stored procedures for the data retrieval and data manipulation. The other issue here is the ORM. This guy needs to go, but not without a better plan. Unfortunately as is the case many times developmenet see's the database and platform in general as just a place to persist data without any respect for the decades of work around relational engines and the incredible things you can do now with SQL 2008. All intelligent thinking has to come from product development and then those decisions are pushed down. I am sure many of you understand ORM's so I won't go into how our operates specifically other than to say it creates the sql based on a xml template as well as generating the DAO's for the higher code. Does anyone have any experience in this and getting away from ORM's? I know that much of this work will be upon myself, but I am fine with that if it helps us become more efficient and because such more profitable. I know that Database Projects in Visual Studio 2008 will be the item filling a large gap here since it can handle database code refactoring and even set policies regarding bad sql code.
Anyway there are a lot of things in motion here and I am trying to figure out best how to deal with each one to ultimately create the full system. I would appreciate any help per the items I listed above.
Thanks
March 11, 2010 at 9:11 am
We had to yield to some ORM usage where I work, luckily usage is controlled as of now and limited to one small application that runs a job once daily. We carefully monitor the 'chattiness' and procedure cache usage. The first attempt they had lots of query plans that were not being reused but they fixed it next time with some setting in NH not sure what but it reuses plans as of now. We keep the ORM model and strongly regulate what they use so right now it seems alright but I dont know about the future. I wish DBAs had enough power to get rid of stuff like this but really as support personnel our powers are limited. I keep watching Grant's blog for more info on NH related issues.
March 11, 2010 at 9:23 am
Closed circle then, because I'm reading this stuff to try to understand what I might be seeing in the future.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 5, 2022 at 5:02 pm
This was removed by the editor as SPAM
March 7, 2022 at 11:21 am
This was removed by the editor as SPAM
Viewing 5 posts - 76 through 79 (of 79 total)
You must be logged in to reply to this topic. Login to reply