10,828 reputation
23165
bio website
location Connecticut
age
visits member for 3 years, 5 months
seen 54 mins ago

I'm a dude.


1d
revised Database is happily available even during log growth?
added 3624 characters in body
1d
revised Database is happily available even during log growth?
deleted 3763 characters in body
Oct
6
comment Should these two indexes suggested by the SSMS missing index feature be combined?
Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.
Oct
3
comment T-SQL - OPTION (FAST x) and trace flag 8722
DBA.SE - More SQL Server MVP's per capita than the PASS Summit.™
Sep
30
awarded  Explainer
Sep
29
comment rebuild on clustered index, why does datasize shrink?
Are you determining data size from the file size?
Sep
25
comment Whats wrong with my index?
I'm not sure about MySQL, but in for instance SQL Server when you apply a function to a column in the WHERE clause (such as your date() function) it negates the use of indexes because it forces a table scan.
Sep
24
awarded  Autobiographer
Sep
23
comment 700 million row mixed text/numeric data - read-only in a single table
This isn't huge at all for a reporting dataset with bulk loads. Assuming your average column is about 6 bytes, you're talking about 140GB total data. I can't imagine someone in charge of managing data who can't imagine that SQL Server would have an issue handling this.
Sep
22
comment Alternatives for a LEFT OUTER JOIN query
Also I would be very wary of using a product that has bugs in such a basic operation as an OUTER JOIN
Sep
22
comment Alternatives for a LEFT OUTER JOIN query
If you need to actually get data from a table that may or may not have a matching record, it's gonna be very messy if you can't use an OUTER JOIN.
Sep
22
comment Deadlock when CODES table has 41900 rows
That makes sense, as you're updating a column in an index and if that index is used by any other process it may contribute to a deadlock.
Sep
16
comment Deadlock when CODES table has 41900 rows
Step 1 - drop the index CODES_IS_COMM_IX - it gives you nothing that CODES_IS_TRANS_COMM_FLAG_IX doesn't already have, but both indexes need to be maintained
Sep
16
comment Deadlock when CODES table has 41900 rows
One thing I see right away is you have redundant indexes which is not gonna help...
Sep
16
comment Deadlock when CODES table has 41900 rows
On indexes you set "Allow Page Locks" and "Allow Row Locks" as options. If those are OFF then then entire index gets locked.
Sep
16
comment Deadlock when CODES table has 41900 rows
Some things to check whenever there are deadlocks - what indexes are involved? What are your page and row locking options for affected indexes (including CI)? What's your isolation level?
Sep
15
reviewed Reject suggested edit on Duplicate login details per user in SQL Server 2008 R2 Error Log
Sep
11
comment Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements
I suspect you'll need to look at XML indexes to optimize this. These operations are VERY CPU-intensive otherwise.
Sep
8
comment A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?
Again, I don't know for Oracle. In SQL Server it can if you are inserting into the middle of the index like I assume you would have to be for non-time and non-id indexes. The DB has to maintain the b-tree for that whole index range which can be not-inexpensive.
Sep
8
comment A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?
Well reducing the rows shouldn't impact that TOO much, but I know very little about Oracle.