March 12, 2014 at 6:43 pm
Luis Cazares (3/12/2014)
dwain.c (3/12/2014)
Luis Cazares (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
I'm sure we can help. Here's a possible solution for you:
BWAHAHAHAHAHAA!
Nice one Luis!
I've been tempted to do something like this several times (I might have done it).
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀
But... but... but we've never done it THAT way before. We've always done it the other way. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2014 at 6:44 pm
Luis Cazares (3/12/2014)
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀
That's a cool story Luis. I just wish I could get that point across to some of the folks I work with.
We've got a customer using a high transactional volume database that many developers have touched, and consequently through lack of knowledge have added lots and lots of indexes. I ran across an index on a really wide table (probably the widest in the entire application) that included in the key something like 80% of the table's columns. They've been complaining about how in general transaction processing was slow.
My advice: drop all the bloody indexes and start from scratch, adding back only what's needed one at a time. The blank look that I got was pretty impressive.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2014 at 7:03 pm
dwain.c (3/12/2014)
Luis Cazares (3/12/2014)
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀That's a cool story Luis. I just wish I could get that point across to some of the folks I work with.
We've got a customer using a high transactional volume database that many developers have touched, and consequently through lack of knowledge have added lots and lots of indexes. I ran across an index on a really wide table (probably the widest in the entire application) that included in the key something like 80% of the table's columns. They've been complaining about how in general transaction processing was slow.
My advice: drop all the bloody indexes and start from scratch, adding back only what's needed one at a time. The blank look that I got was pretty impressive.
People who think that they know, even if the don't, are always more dangerous than those who know their limitations and are trying to learn and get second opinions.
March 12, 2014 at 7:04 pm
dwain.c (3/12/2014)
Luis Cazares (3/12/2014)
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀That's a cool story Luis. I just wish I could get that point across to some of the folks I work with.
We've got a customer using a high transactional volume database that many developers have touched, and consequently through lack of knowledge have added lots and lots of indexes. I ran across an index on a really wide table (probably the widest in the entire application) that included in the key something like 80% of the table's columns. They've been complaining about how in general transaction processing was slow.
My advice: drop all the bloody indexes and start from scratch, adding back only what's needed one at a time. The blank look that I got was pretty impressive.
That is what the Experts call a 'covering index'.
Although it probably never gets used, it does do something.
Might be interesting to see what indexes actually are getting used.
March 12, 2014 at 7:39 pm
Greg Edwards-268690 (3/12/2014)
dwain.c (3/12/2014)
Luis Cazares (3/12/2014)
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀That's a cool story Luis. I just wish I could get that point across to some of the folks I work with.
We've got a customer using a high transactional volume database that many developers have touched, and consequently through lack of knowledge have added lots and lots of indexes. I ran across an index on a really wide table (probably the widest in the entire application) that included in the key something like 80% of the table's columns. They've been complaining about how in general transaction processing was slow.
My advice: drop all the bloody indexes and start from scratch, adding back only what's needed one at a time. The blank look that I got was pretty impressive.
That is what the Experts call a 'covering index'.
Although it probably never gets used, it does do something.
Might be interesting to see what indexes actually are getting used.
If that index was really needed for something (which I personally think not), it might be a little better done if most of the columns were in an INCLUDE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 13, 2014 at 5:40 am
dwain.c (3/12/2014)
Greg Edwards-268690 (3/12/2014)
dwain.c (3/12/2014)
Luis Cazares (3/12/2014)
In another order of ideas, today I had a good experience to prove your quote about indexing and cat food. A process with an insert and update to the same table was taking almost 2 hours to finish and the update used 90 minutes. After trying to create an index that would work for this query, I proposed to remove the update and include the value in the insert. The insert didn't take longer and we could deliver the information one and a half hour faster each day. 😀That's a cool story Luis. I just wish I could get that point across to some of the folks I work with.
We've got a customer using a high transactional volume database that many developers have touched, and consequently through lack of knowledge have added lots and lots of indexes. I ran across an index on a really wide table (probably the widest in the entire application) that included in the key something like 80% of the table's columns. They've been complaining about how in general transaction processing was slow.
My advice: drop all the bloody indexes and start from scratch, adding back only what's needed one at a time. The blank look that I got was pretty impressive.
That is what the Experts call a 'covering index'.
Although it probably never gets used, it does do something.
Might be interesting to see what indexes actually are getting used.
If that index was really needed for something (which I personally think not), it might be a little better done if most of the columns were in an INCLUDE.
You're using INCLUDEd columns? <sarcasm>And here I thought we were just supposed to keep creating more and more redundant indexes on the same combination of columns over and over again until SQL recognized one and started using it. It doesn't matter how many there are...just keep creating more and SQL will eventually catch up. You mean that's not right? Could index maintenance possibly be important? How could this be? </sarcasm> 😉
March 13, 2014 at 6:35 am
Jeff Moden (3/12/2014)
But... but... but we've never done it THAT way before. We've always done it the other way. 😛
If only I could estimate how many times I've heard that before. :crazy:
If the way we've always done it doesn't work, then FIX IT!
March 13, 2014 at 7:03 am
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 13, 2014 at 7:23 am
GilaMonster (3/11/2014)
Koen Verbeeck (3/11/2014)
Grant Fritchey (3/11/2014)
By the gods, people get hung up on the weirdest darned things. They can focus like a laser on stuff that's just a waste of time. It's weird.I think we're talking about the same topic 😀
Yup.
GilaMonster (3/11/2014)
Don't make weird, non-standard design decisions based on an unfounded expectation of some unknown performance improvement.
This one is going on the cubicle wall!
Jason Wolfkill
March 13, 2014 at 8:14 am
andrew gothard (3/13/2014)
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
Sorry to be so long in replying. Had problems with my laptop after backing up both my D: and C: drives per your instructions. While a technician is looking at it, a co-worker is letting me borrow their laptop so I can send this. Where do the FDISK backups reside? Do I still need to do a FDISK backup of the Z drive?
My database still isn't working.
Thanks for all the constructive suggestions!! 🙂 Although my boss is unhappy that we can't do business, I've at least been able to explain to him that some of the best SQL experts are working on our problem, but that these things take time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 13, 2014 at 8:28 am
The Dixie Flatline (3/13/2014)
andrew gothard (3/13/2014)
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
Sorry to be so long in replying. Had problems with my laptop after backing up both my D: and C: drives per your instructions. While a technician is looking at it, a co-worker is letting me borrow their laptop so I can send this. Where do the FDISK backups reside? Do I still need to do a FDISK backup of the Z drive?
My database still isn't working.
Thanks for all the constructive suggestions!! 🙂 Although my boss is unhappy that we can't do business, I've at least been able to explain to him that some of the best SQL experts are working on our problem, but that these things take time.
yes z should be fdisked. Here is a script routine to make that easier on you.
Erase hard drive and clear CMOS
Before doing the below routine ensure that you read our disclaimer as well as are sure that this is safe to do on your hard drive. Some computer hard drive manufacturers may void your warranty or this may cause damage to the hard drive when doing the below example.
This routine is useful in clearing DDO and removing possible viruses. If this routine completes successfully it will erase everything from your Hard drive as well as your reset your CMOS values.
In the below debug routine, only type what is in bold from line 3 to 17, you will get a response if typed in properly. If you get a response continue to type the bold text as shown below in the below example.
A:\>debug <enter>
/*deleted the guts of this. Even though the entire script can be found on the internet, somebody is bound to try it from here without thinking that this whole thing was just a joke */
-xxxx:0123 <enter> (without typing anything.)
-g <enter>
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
March 13, 2014 at 8:29 am
The Dixie Flatline (3/13/2014)
andrew gothard (3/13/2014)
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
Sorry to be so long in replying. Had problems with my laptop after backing up both my D: and C: drives per your instructions. While a technician is looking at it, a co-worker is letting me borrow their laptop so I can send this. Where do the FDISK backups reside? Do I still need to do a FDISK backup of the Z drive?
My database still isn't working.
Thanks for all the constructive suggestions!! 🙂 Although my boss is unhappy that we can't do business, I've at least been able to explain to him that some of the best SQL experts are working on our problem, but that these things take time.
C: drive goes to the Cloud, which can take longer.
Did the previous DBA leave you some envelopes?
I seem to recall they can be used in such an emergency.
Glad to see you are willing to wait and staying calm.
I've seen sometimes where shutting off the power to nearby servers can speed up the process, although I am not sure which one you should try first.
March 13, 2014 at 8:32 am
Greg Edwards-268690 (3/13/2014)
The Dixie Flatline (3/13/2014)
andrew gothard (3/13/2014)
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
Sorry to be so long in replying. Had problems with my laptop after backing up both my D: and C: drives per your instructions. While a technician is looking at it, a co-worker is letting me borrow their laptop so I can send this. Where do the FDISK backups reside? Do I still need to do a FDISK backup of the Z drive?
My database still isn't working.
Thanks for all the constructive suggestions!! 🙂 Although my boss is unhappy that we can't do business, I've at least been able to explain to him that some of the best SQL experts are working on our problem, but that these things take time.
C: drive goes to the Cloud, which can take longer.
Did the previous DBA leave you some envelopes?
I seem to recall they can be used in such an emergency.
Glad to see you are willing to wait and staying calm.
I've seen sometimes where shutting off the power to nearby servers can speed up the process, although I am not sure which one you should try first.
Love the envelope reference
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
March 13, 2014 at 8:40 am
SQLRNNR (3/13/2014)
Greg Edwards-268690 (3/13/2014)
The Dixie Flatline (3/13/2014)
andrew gothard (3/13/2014)
The Dixie Flatline (3/12/2014)
SQLRNNR (3/12/2014)
The Dixie Flatline (3/12/2014)
I have a database. It doesn't work. Please advise.Thanks in advance.
P.S. Please don't ask for DDL and sample data as I've already said that it doesn't work.
Dear expert, you should drop your database.
Okay, I dropped the database but it still doesn't work. Someone on another website says I should do a backup, but wouldn't tell me how. Would you agree that this is a good idea? If so, could you give me instructions? If you don't agree, could you talk to him about this for me?
This is entirely correct. You need to back it up From Disk, from the drive you store your data on. The command to backup From Disk is just an abbreviation of From Disk; FDISK - so, to back up your database files on D:\ just open up a dos command window and run FDISK D
Sorry to be so long in replying. Had problems with my laptop after backing up both my D: and C: drives per your instructions. While a technician is looking at it, a co-worker is letting me borrow their laptop so I can send this. Where do the FDISK backups reside? Do I still need to do a FDISK backup of the Z drive?
My database still isn't working.
Thanks for all the constructive suggestions!! 🙂 Although my boss is unhappy that we can't do business, I've at least been able to explain to him that some of the best SQL experts are working on our problem, but that these things take time.
C: drive goes to the Cloud, which can take longer.
Did the previous DBA leave you some envelopes?
I seem to recall they can be used in such an emergency.
Glad to see you are willing to wait and staying calm.
I've seen sometimes where shutting off the power to nearby servers can speed up the process, although I am not sure which one you should try first.
Love the envelope reference
Me too. That was awesome!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 9:29 am
I looked but I couldn't find even one envelope, much less three. My boss called me in and told me I was fired, even though I tried to explain to him that I hadn't finished backing up the Z drive yet. :w00t: So now, I am unjustly looking for work. If any of you know of any high-paying jobs for someone with SQL expertise, please let me know. Also I may be asking for help with some interview questions. I invite you all to join me on LinkedIn and give me personal references. Thanks again for all your help.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 43,291 through 43,305 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply