January 23, 2014 at 8:32 pm
Comments posted to this topic are about the item Common Checks
January 24, 2014 at 1:55 am
Fragmentation
Fullness of files and prediction for when we will have to grow them. We don't want autogrowth in large production systems we like to plan it.
Absence of clustered indexes
Absence of indexes
System named constraints rather than explicitly named constraints. Not much point in having a schema compare tool unless you do this!
Identity ranges as per your editorial
Use of SQL reserved words in objects
Permissions granted to named individuals rather than AD Security groups
Direct access to objects rather than access via a role.
Job run time alerting. Why does something that should take 10 minutes suddenly take an hour.
Unusual data volumes (low or high) or growth beyond what was expected.
January 24, 2014 at 2:09 am
we monitor system activity
- users logging in
- data updates (people data feeds)
- changes to people structures
- emails sent from the system
- successful backups
- successful re-indexing of the database tables
Helps us to ensure the use and operation is within bounds and that the automated tasks are running.
January 24, 2014 at 4:25 am
We monitor running time of reindex jobs and stop the job it goes over x hrs.
January 24, 2014 at 5:49 am
"While we considered changing to a bigint, this was a third party product and they did not want to allow us to alter the schema."
There's your problem. Perhaps testing the product at expected volumes would have exposed this shortcoming. The company's refusal to adapt and correct an obvious flaw would automatically generate an RFP process in my world.
January 24, 2014 at 6:41 am
Dev Environment:
When we get a database related bug, we consider whether we can write a check to catch that type of bug in the future. We go beyond the scope of the specific bug that was found.
Most of the checks were added years ago and we rarely get bugs in production related to careless design mistakes.
One example is a set of checks that were added to deal with bugs related to inconsistent schema or coding standards. For example, there is one that searches for fields with the same name in different tables with inconsistent data types. This also help us to find bugs with the same logical root that were not identified until the check was written.
January 24, 2014 at 7:14 am
phegedusich (1/24/2014)
"While we considered changing to a bigint, this was a third party product and they did not want to allow us to alter the schema."There's your problem. Perhaps testing the product at expected volumes would have exposed this shortcoming. The company's refusal to adapt and correct an obvious flaw would automatically generate an RFP process in my world.
Surely without knowing the background these sweeping statements are a little difficult to justify. How long had the software been in? What were the volumes (including projected ones) at the time of purchase? What alternatives, if any, existed at the time? What costs were involved in any proposed changes?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 24, 2014 at 7:25 am
One thing we do is we have a job the checks every hard drive on every server. It imports the bytes used on each drive along with total bytes available. We then have a SSRS report on our IT intranet site that displays that info if it gets to 30% free, turns yellow at 20% and red at 10%.
January 24, 2014 at 9:53 am
phegedusich (1/24/2014)
--------------------------------------------------------------------------------
"While we considered changing to a bigint, this was a third party product and they did not want to allow us to alter the schema."
There's your problem. Perhaps testing the product at expected volumes would have exposed this shortcoming. The company's refusal to adapt and correct an obvious flaw would automatically generate an RFP process in my world.
Surely without knowing the background these sweeping statements are a little difficult to justify. How long had the software been in? What were the volumes (including projected ones) at the time of purchase? What alternatives, if any, existed at the time? What costs were involved in any proposed changes?
-----------------------------------------
+1000 on that one Gary.
January 24, 2014 at 10:26 am
Untrusted, but enabled constraints (autofixed when possible)
Index fragmentation (autofixed)
Database owner not "sa"
Too many VLF's or too few MB per VLF
Autoshrink
Autoclose
Log files almost full
Data files almost full
Page verify other than CHECKSUM
Full recovery model databases without T-log backups
Any connection that's not encrypted
I'd like to add:
OS level fragmentation (but smart, using MB per fragment for DB files)
Excessive numbers of failed login attempts
Dynamic SQL ports
Backups not as current as required
Restores not as current as required, etc.
Sysadmin permissions other than "sa" and DBA's
Db_owner permissions other than "sa" and DBA's and a few third party accounts
Weak passwords
Service accounts with administrator access (local)
Service accounts with administrator access (domain)
Service accounts without standard group policy settings (Perform volume maintenance, etc.)
Default Fill Factor not recommended
Max memory too high
Cost Threshold for Parallelism too low
Antivirus not enabled
Antivirus not doing on-access scanning
Backup compression not on
January 24, 2014 at 4:13 pm
[
There's your problem. Perhaps testing the product at expected volumes would have exposed this shortcoming. The company's refusal to adapt and correct an obvious flaw would automatically generate an RFP process in my world.
The number of 3rd party systems I have seen with substandard data models is......job security for life;-)
There is only so much time that can be spent on testing a 3rd party system. If it requires extensive testing then why am I buying it it the first place? The resource requirements are so high I might as well build my own!
As for triggering an RFP I'd suggest it would trigger an impact assessment first. Yes its a pain to reseed identities but is it enough of a pain to make a full scale product switch necessary and cost effective. Software switches are immensely risky. There is the 70% of features that people remember to ask about, the 15% of pain they want fixed and the 15% of features they use but take completely for granted. In the replacement product you won't get a like-for-like feature match and coupled with a proportion of taken-for-granted features that simply aren't in the new product you are right back at square one. 70% fine, 15% pain and a different 15% taken for granted!
January 26, 2014 at 11:11 pm
I have little scripts that run once a week to capture size of each datadase, as well as the size of each of the hard drive groups in the SAN (data, log, temp).
This allows us to see any irregular changes that may occur on the db or h/disk side
January 27, 2014 at 7:32 am
In addition to those already listed by others, I added checks for:
- Disabled Indexes (no way to tell in the GUI that I am aware of)
- Auto Create/Update statistics set to Off
- Number of active connections too high
January 28, 2014 at 1:10 pm
phegedusich (1/24/2014)
"While we considered changing to a bigint, this was a third party product and they did not want to allow us to alter the schema."There's your problem. Perhaps testing the product at expected volumes would have exposed this shortcoming. The company's refusal to adapt and correct an obvious flaw would automatically generate an RFP process in my world.
The sales process and the scale at which it was decided to run this was disconnected. The other issue is the same one at many large companies. We aren't always buying software on technical merits. There's a reason salespeople exist; they sell the product.
January 31, 2014 at 9:28 am
here's one that i just got bitten with and added to my common checks:
i added a filtered index, and a stored proc that updated the table with the filtered index started failing because it was created with quoted identifier = off instead of on;
my user databases should always have quoted identifier true/ansi nulls true for every object;
anything this returns would need to be altered with the settings corrected.
select * from (
select object_name(object_id) as ObjectName,
objectproperty(object_id,'ExecIsQuotedIdentOn') AS 'IsQuotedIdentifier',
objectproperty(object_id,'ExecIsAnsiNullsOn') as 'ISAnsiNulls'
from sys.objects
) X WHERE IsQuotedIdentifier = 0
Lowell
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply