182 reputation
29
bio website masteredsoftware.com
location Los Angeles, CA
age 36
visits member for 2 years, 11 months
seen Aug 24 at 0:43

I am a Software Engineer working for Maker Studios building web-based NodeJS applications. In my spare time I sometimes play and develop games and other applications under my company: Mastered Software, you can play my space strategy game: Astriarch - Ruler of the Stars.


13h
awarded  Notable Question
Oct
1
awarded  Notable Question
Aug
24
comment Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
It's a creative approach. However, as you say you'll only have 350,400 rows in your combined datetime dim table, if you start changing the grain to finer resolution, you'll quickly get into the millions of records. If you choose to have a separate date dimension than time dimension you only have 48 rows in your time dimension table and only 365 rows per year in your date dimension table (or 7300 rows in 20 years). Your fact table then simply has a column for date_key and time_key. This also makes it more flexible if you have some fact tables that only require date granularity.
Feb
14
awarded  Commentator
Feb
14
comment Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
@AaronBertrand it depends on the grain (or granularity you choose) to track your data at, in our case we only needed 15 minute granularity in our fact tables so it's only 4 * 24 = 96 records per day per timezone we wanted to support, which is completely reasonable.
Feb
12
awarded  Popular Question
Sep
28
awarded  Teacher
Sep
27
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
I never was able to get to the bottom of the problem, but I assume you are correct.
Sep
27
accepted SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
May
14
awarded  Popular Question
Mar
25
awarded  Popular Question
Oct
2
comment Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?
Thanks for that, very helpful to know that my idea doesn't sound like it would do any good.
Oct
2
accepted Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?
Oct
2
asked Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?
Jul
17
comment Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
Our primary clustered key for the bridge table is on the utc date/time columns + the timezone id (if I remember correctly), since all the fact tables time keys will be in utc, you'll be joining to the bridge through the utc keys + tz id, it might work better to have the clustered index on those. Do what makes sense for your needs though. I'm glad my answer helped someone, I think it is a good approach and from all of our testing, it is still reasonably fast, just be careful when it comes to the WHERE clause: filter out the date ranges you want as early as possible in your queries.
Jun
6
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
So far I have not gotten any database crc errors (or connection pool errors) since I've turned off scheduled index reorganization/rebuilding while under load. Perhaps I'm testing on h/w that is insufficient or perhaps I need Sql Server enterprise's feature of rebuild index online=on? I'll keep testing and maybe I can come up with a viable cause and possible solutions. Thanks for your help guys
May
31
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
Thanks darin, I'll continue to investigate this. I've turned off all index tuning jobs for now to see if I can get consistency errors without it. This server doesn't have a RAID or SAN, it is a local physical disk. I've also added many more performance counters to see if I can track it down.
May
30
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
Thanks Jon, I was looking at those scripts yesterday trying to understand them, kinda heavyweight, but might be helpful to close the gaps where I might be doing something wrong. I'll take a look to see if I get the problem when the system isn't busy.
May
29
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
CHKDSK found no errors Sql Server Error logger shows more of the same issues as the Event log. I do see these connection pool starvation issues too: The client was unable to reuse a session with SPID 87, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. and Error: 18056, Severity: 20, State: 29.
May
29
comment SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
Event Log shows similar info as the job history error: Read on "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname.mdf" failed: 23(Data error (cyclic redundancy check).) and Unable to read and latch page (1:7699681) with latch type SH. 23(Data error (cyclic redundancy check).) failed. This is physical hardware, server machine Xeon processor, I'll run a chkdsk to see if there are any disk errors. This has happened to two different indexes (I've only had the error crop up twice). I'll also check sql server error log.