November 4, 2011 at 6:51 am
Evil Kraig F (11/3/2011)
Anyone else been having serious problems with posting today? Edits, quotes, even just add reply is being feisty. Won't seem to trigger the post creation page, then it takes two or three tries of 'post this' to get it to go through, and half the time it won't take.Getting annoying.
No problem on that for me. SSC must just be catching on to you and trying to block your plans for world domination through offering SQL advice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 6:51 am
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Very cool!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 7:21 am
GSquared (11/4/2011)
Kiara (11/3/2011)
GSquared (11/3/2011)
I just found a stored procedure from my predecessor at my current job, and it just kind of made my jaw drop in the elegant simplicity of it's complete stupidity.It looked basically like this:
create proc dbo.MyProcas
set nocount on;
declare @TableVariable (
columns, including PK definition)
insert into @TableVariable
select distinct columns
from tables
where business rules
select distinct * from @TableVariable
The actual query involves several computed columns and joins between four different tables. It works as designed.
I discovered this query because it started to throw a primary key violation on what everyone here thought was just a Select statement. Note that it doesn't actually DO anything with the table variable except select from it. No data modifications. Not even a Where clause on the select.
How does someone come up with a design like that? Why? What weird logic process leads to doing all that just to select some data.
Sorry, had to vent, and thought you guys might get some entertainment out of this.
PS: Even our business analyst thought this one was a bit odd in terms of working a Select that way. He doesn't even write code.
I can only think of one reason - and the only reason I can actually think of it is because I just had to write something similar. (Similar, I said. Not exactly like.) Before you all start throwing tomatoes and other rotten fruit at me...
I have a situation where one of the applications I don't manage makes calls to SQL to pull data. I can stage the data for that app in a matter of ms. However, it can take that program 10-15 minutes to actually process it - the wait stats on the process can be amazingly annoying. Oh - and that program asks for new data literally every 5 minutes.
What was originally happening was that because that app can't consume the data anywhere near as quickly as I can send it, I had locks sitting on tables that other people were trying to use, but couldn't get to until the app was finished. (I got our dev folks to radically change their design, but ultimately I'm passing data to a noSQL environment that can't do set-based reads. Long story, many details, options, and other attempts to fix the problem left out of the mix here.)
The end fix was very similar to the code above. I'm basically having that program call a stored proc with a couple of parameters - then loading the data that will be returned into a table variable. Quick select against my normal db tables, locks released quickly, happy users. Then, when I select from that table variable, that connection can sit and do it's row by row chunking through the data I sent it to its heart's content without sending my side of the universe into block and deadlock hell.
So - I had a very practical reason to write something along those lines (and if someone wants to show me a better way to handle that situation, I'm happy hear it - just because I found something that both works and makes my server happy doesn't mean I'm convinced that it's either the only way or the best way to solve a particular challenge.)
I'm curious as to what the proc you found is being used for...
Your situation sounds like a good case for Snapshot Isolation. Have you tried that?
This one is used to populate a list on a web page. It doesn't even hold the connection open after that, much less lock the data.
I hadn't tried that route for a couple of reasons. First, I don't currently have a separate drive for tempdb, but I have a lot of available RAM, I tend to use memory rather than tempdb when possible. Second, since I haven't worked with snapshot isolation much, it's not my first go-to option when I'm in a hurry, since I just don't have enough experience with it to know where it's a good fit and where it isn't.
However... I just got replacement server hardware authorized (amazing how cheap new hardware is compared to my time to write code that works around our existing hardware...). (There are several running jokes around here that involve things like "spindles are a girl's best friend" and "all I want for Christmas are a few more spindles" pertaining to me...)
So... I'll do some testing as soon as the new server is in place. This isn't the only situation I've got where snapshot isolation may make my life a lot easier. (I live in a world where everyone wants data from our OLTP system "real time" - so I spend a lot of my life making sure that new data requests can run concurrently in that environment without impacting the normal users.)
Thanks again. I really, really love how much I learn from my time lurking over here!
-Ki
November 4, 2011 at 7:29 am
Ki,
You do remember that Table Variables use TempDB, right?
November 4, 2011 at 7:34 am
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Good for her! She must be excited.
--------------------------------------
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
November 4, 2011 at 7:35 am
Brandie Tarvin (11/4/2011)
Ki,You do remember that Table Variables use TempDB, right?
Yup. But when I've been testing, they seem to do less r/w activity than temp tables do. Could just be my specific environment - I've got a less than optimal drive setup over here.
-Ki
November 4, 2011 at 7:37 am
Brandie Tarvin (11/4/2011)
WHOOT! Just got my author's copy of Space Tramps in.Space Tramps information:
Amazon link: http://www.amazon.com/Space-Tramps-Full-Throttle-Tales/dp/0984592741
ISBN numbers, if you care to order it from your local bookstore (which I highly encourage!):
ISBN-10: 0984592741
ISBN-13: 978-0984592746
If you do happen to read it, I'd love to hear reviews.
Very nice.
--------------------------------------
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
November 4, 2011 at 7:41 am
Kiara (11/4/2011)
Brandie Tarvin (11/4/2011)
Ki,You do remember that Table Variables use TempDB, right?
Yup. But when I've been testing, they seem to do less r/w activity than temp tables do. Could just be my specific environment - I've got a less than optimal drive setup over here.
That's because they write less to the transaction log, not because they have fewer IOs (they don't) to tempdb, I think.
Tom
November 4, 2011 at 7:46 am
L' Eomot Inversé (11/4/2011)
Kiara (11/4/2011)
Brandie Tarvin (11/4/2011)
Ki,You do remember that Table Variables use TempDB, right?
Yup. But when I've been testing, they seem to do less r/w activity than temp tables do. Could just be my specific environment - I've got a less than optimal drive setup over here.
That's because they write less to the transaction log, not because they have fewer IOs (they don't) to tempdb, I think.
That fits what I saw when testing - and just means I drew the wrong conclusion from my (albeit limited) data. Thanks for the clarification.
-Ki
November 4, 2011 at 7:58 am
Kiara (11/4/2011)
L' Eomot Inversé (11/4/2011)
Kiara (11/4/2011)
Brandie Tarvin (11/4/2011)
Ki,You do remember that Table Variables use TempDB, right?
Yup. But when I've been testing, they seem to do less r/w activity than temp tables do. Could just be my specific environment - I've got a less than optimal drive setup over here.
That's because they write less to the transaction log, not because they have fewer IOs (they don't) to tempdb, I think.
That fits what I saw when testing - and just means I drew the wrong conclusion from my (albeit limited) data. Thanks for the clarification.
I would think that judicious use of temp tables/table variables would have less impact on tempdb than snapshot isolation since everything is going to use the version store in tempdb vs just the use by the temp tables/table variables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 4, 2011 at 8:24 am
Jack Corbett (11/4/2011)
I would think that judicious use of temp tables/table variables would have less impact on tempdb than snapshot isolation since everything is going to use the version store in tempdb vs just the use by the temp tables/table variables.
Well let's think about that a bit more. Assuming the server is not under memory pressure (buffer pool pages being written to disk) neither the table variable nor the temporary table data will end up being written to physical storage at all (though there will be some tempdb logging activity as Tom remarked - though again this only results in physical I/O under memory pressure). Nevertheless, we will be using some pool memory to store the copied rows, so it's not 'for free' overall.
In the case where a row-versioning isolation level is used (most commonly RCSI rather than SI) the amount of version store activity depends on concurrent changes to the underlying table. If the table has no changes while the reading transaction is open, no row versions will be generated, and the impact on pool and tempdb is essentially zero. If there are many concurrent changes, and particularly if those transactions are long-running, the version store usage might be significant, and the process reading rows might have to traverse a long chain.
So, it depends 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 4, 2011 at 8:30 am
SQL Kiwi (11/4/2011)
So, it depends
Someone so needs to come up with an interview question where this is the exact answer that's needed.
@=)
November 4, 2011 at 8:38 am
Kiara (11/4/2011)
Brandie Tarvin (11/4/2011)
Ki,You do remember that Table Variables use TempDB, right?
Yup. But when I've been testing, they seem to do less r/w activity than temp tables do. Could just be my specific environment - I've got a less than optimal drive setup over here.
Buy some fusion IO cards
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2011 at 8:40 am
Brandie Tarvin (11/4/2011)
SQL Kiwi (11/4/2011)
So, it dependsSomeone so needs to come up with an interview question where this is the exact answer that's needed.
@=)
I have about 30 of those questions.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2011 at 8:43 am
Brandie Tarvin (11/4/2011)
SQL Kiwi (11/4/2011)
So, it dependsSomeone so needs to come up with an interview question where this is the exact answer that's needed.
@=)
Isn't that pretty much every interview question relating to SQL Server?
Viewing 15 posts - 31,396 through 31,410 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply