List of DMV in SQL Server 2005 and 2008
Posted by Ravi Khanal on Jan 31, 2009
Dynamic Management Views(DMVs) are system views that surface the internal counters of the database engine and help present an easily comprehendible dashboard of the database engine performance that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
List of DMV in SQL Server 2005
1. sys.dm_broker_activated_tasks
2. sys.dm_broker_connections
3. sys.dm_broker_forwarded_messages
4. sys.dm_broker_queue_monitors
5. sys.dm_clr_appdomains
6. sys.dm_clr_loaded_assemblies
7. sys.dm_clr_properties
8. sys.dm_clr_tasks
9. sys.dm_db_file_space_usage
10. sys.dm_db_index_usage_stats
11. sys.dm_db_mirroring_connections
12. sys.dm_db_missing_index_details
13. sys.dm_db_missing_index_group_stats
14. sys.dm_db_missing_index_groups
15. sys.dm_db_partition_stats
16. sys.dm_db_session_space_usage
17. sys.dm_db_task_space_usage
18. sys.dm_exec_background_job_queue
19. sys.dm_exec_background_job_queue_stats
20. sys.dm_exec_cached_plans
21. sys.dm_exec_connections
22. sys.dm_exec_query_memory_grants
23. sys.dm_exec_query_optimizer_info
24. sys.dm_exec_query_resource_semaphores
25. sys.dm_exec_query_stats
26. sys.dm_exec_query_transformation_stats
27. sys.dm_exec_requests
28. sys.dm_exec_sessions
29. sys.dm_fts_active_catalogs
30. sys.dm_fts_index_population
31. sys.dm_fts_memory_buffers
32. sys.dm_fts_memory_pools
33. sys.dm_fts_population_ranges
34. sys.dm_io_backup_tapes
35. sys.dm_io_cluster_shared_drives
36. sys.dm_io_pending_io_requests
37. sys.dm_os_buffer_descriptors
38. sys.dm_os_child_instances
39. sys.dm_os_cluster_nodes
40. sys.dm_os_hosts
41. sys.dm_os_latch_stats
42. sys.dm_os_loaded_modules
43. sys.dm_os_memory_allocations
44. sys.dm_os_memory_cache_clock_hands
45. sys.dm_os_memory_cache_counters
46. sys.dm_os_memory_cache_entries
47. sys.dm_os_memory_cache_hash_tables
48. sys.dm_os_memory_clerks
49. sys.dm_os_memory_objects
50. sys.dm_os_memory_pools
51. sys.dm_os_performance_counters
52. sys.dm_os_ring_buffers
53. sys.dm_os_schedulers
54. sys.dm_os_stacks
55. sys.dm_os_sublatches
56. sys.dm_os_sys_info
57. sys.dm_os_tasks
58. sys.dm_os_threads
59. sys.dm_os_virtual_address_dump
60. sys.dm_os_wait_stats
61. sys.dm_os_waiting_tasks
62. sys.dm_os_worker_local_storage
63. sys.dm_os_workers
64. sys.dm_qn_subscriptions
65. sys.dm_repl_articles
66. sys.dm_repl_schemas
67. sys.dm_repl_tranhash
68. sys.dm_repl_traninfo
69. sys.dm_tran_active_snapshot_database_transactions
70. sys.dm_tran_active_transactions
71. sys.dm_tran_current_snapshot
72. sys.dm_tran_current_transaction
73. sys.dm_tran_database_transactions
74. sys.dm_tran_tran_locks
75. sys.dm_tran_session_transactions
76. sys.dm_tran_top_version_generators
77. sys.dm_tran_transactions_snapshot
78. sys.dm_tran_version_store
New DMV in SQL Server 2008
1. sys.dm_audit_actions
2. sys.dm_audit_class_type_map
3. sys.dm_cdc_errors
4. sys.dm_cdc_log_scan_sessions
5. sys.dm_cryptographic_provider_properties
6. sys.dm_database_encryption_keys
7. sys.dm_db_mirroring_auto_page_repair
8. sys.dm_db_mirroring_past_actions
9. sys.dm_db_persisted_sku_features
10. sys.dm_db_script_level
11. sys.dm_exec_procedure_stats
12. sys.dm_exec_trigger_stats
13. sys.dm_filestream_file_io_handles
14. sys.dm_filestream_file_io_requests
15. sys.dm_fts_fdhosts
16. sys.dm_fts_outstanding_batches
17. sys.dm_os_dispatcher_pools
18. sys.dm_os_dispatchers
19. sys.dm_os_memory_brokers
20. sys.dm_os_memory_node_access_stats
21. sys.dm_os_memory_nodes
22. sys.dm_os_nodes
23. sys.dm_os_process_memory
24. sys.dm_os_spinlock_stacks
25. sys.dm_os_sys_memory
26. sys.dm_resource_governor_configuration
27. sys.dm_resource_governor_resource_pools
28. sys.dm_resource_governor_workload_groups
29. sys.dm_server_audit_status
30. sys.dm_tran_commit_table
31. sys.dm_xe_map_values
32. sys.dm_xe_object_columns
33. sys.dm_xe_objects
34. sys.dm_xe_packages
35. sys.dm_xe_session_event_actions
36. sys.dm_xe_session_events
37. sys.dm_xe_session_object_columns
38. sys.dm_xe_session_targets
39. sys.dm_xe_sessions
How to migrate DTS Package into SSIS 2008 package
Posted by Ravi Khanal on Jan 10, 2009
If You are planning to migrate DTS Package of SQL Server 2000 to SQL Server 2008’s SSIS Package then first open the Sql Server Management Studio. Connect to the database engine and open the Migration Wizard as shown in the image.
10 Technology Forecast for the year 2009
Posted by Ravi Khanal on Dec 31, 2008
There would be lots of ups and downs in the world of technology in year 2009. Ten things that might happen in the year 2009 would be:
1. Google’s advertizing model will start facing problems:
Google will have some problems with their advertizing model in the year 2009 and advertisers would be less interested in Google’s ads. Google’s ad should not be context based but rather be user based. Google should read the mind of the user and display the ad, exactly what he is looking for. There should be more localization in the Google’s ads. Now a day people are not so much interested in the Google’s ads, so either Google has to change its advertizing model or it will start facing problems.
2. Microsoft will inch ahead due to Google’s loss of reputation:
Year 2009 will be really bad for the Google. Google is acting as a spy and tracking all the data from cookies. Everybody will think twice before using Google. So the charm of Google is reducing day by day and it will slowly fall behind in the race against Microsoft.
3. Standards will evolve in the Clouds:
Till now everybody has defined cloud according to their way. But in the year 2009, some standards will evolve in the clouds and there will be SLAs .
Whats new with SQL Server 2008
Posted by Ravi Khanal on Dec 22, 2008
Improvement in Business Intelligence
- Enterprise Reporting Engine
There are two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.
- Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
In Data warehouses, spaces used to be always wasted due to the NULL values. Columns having lots of NULL value is actually allocating lots of space without storing any values. This problem has been taken care in SQL Server 2008 by the introduction of a sparse columns. This columns allows NULL values to be stored without taking any physical space on the disk.
T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.
How to Synchronize Logins and manage Orphaned Users
Posted by Ravi Khanal on Dec 19, 2008
Database movement between servers is one of the common task for the DBA. But when the database is moved from one server to another or from one instance to other, the logins do not get transferred. So as a DBA you have to manually transfer the logins to remove this problem.
So let’s discuss what is the cause behind this. SQL Server logins are stored in the master database and they are mapped against individual databases. The Login informations on SQL Server 2005 are visible through sys.server_principals system catalog view and the sys.logins view. The mapping information of the database user is stored in the sysusers system table. This table includes the name of the database user and the SID of the corresponding SQL Server Login. The SID is the security identifier and SID tracks the logins throughout the entire systems.
So when we move or copy our database from one server to another or from one instance to another then the windows login will have the same SID but the SQL logins may have the different SIDs. So when the SID of the new server instance and the older instance doesn’t matches we have a Orphaned User.
SSIS Package for merging data sources and applying transformations
Posted by Ravi Khanal on Dec 14, 2008
Scenario:
I have file in different heterogeneous sources like Text file and Excel File and I want to merge it into the SQL Server Table. But before loading the data into SQL Server table I have to change the First Name Column into Upper Case and also I need to give 20% raise in the salary. So I should have the proposed salary column added to the table.
Solution:
So First I need to have the data flow task for this.
Then in the data flow the package would be like this:
SQL Server 2008’s Installation Snapshot
Posted by Ravi Khanal on Dec 11, 2008
How to remove fragmentation in table?
Posted by Ravi Khanal on Dec 8, 2008
Hey, Our Application is down!
This is the common thing you hear while you are working as a DBA. The reason for this is not always evident. Perhaps the number of transactions issued has increased or maybe the volume of the data has increased. But sometimes this might not be the cause of the problem. The problem might be due to disorganization of the database. Database disorganization occurs when a database’s logical and physical storage allocation contains many scattered areas of storage that are too small, not physically contiguous, or too disorganized to be used.
Database disorganization or fragmentation can occur when modifying data with INSERT, UPDATE, or DELETE statements, which over time cause gaps in each page. If a query search is based on a table scan or partial table scan, then it will create overhead for the SQL Server process with additional page reads, leading to high CPU activity and unresponsiveness.
Hadoop and Bigtable
Posted by Ravi Khanal on Nov 25, 2008
Are Apache Hadoop and Google’s Bigtable becoming the major threat for the relational database?
The relational database is the dominent database till now. When Object Oriented Database came into market. Everybody at that time expected that Object Oriented Database was the evolutionary step in the database and it would replace all the relational databases. But it never happened. The main thing we should note is that although Object Oriented Programming language is so popular, the Object Oriented Database failed. If we look closely for the failure of OODBMS’s against traditional RDBMS then its due to the lack of interoperability with a great number of features and tools that we use with SQL like reporting tools, OLAP tools, backups and recovery standards. Moreover OODBMS had weakness on query support due to the lacking of formal mathematical foundation.
Read the rest of this entry »
SQL Injection Attack
Posted by Ravi Khanal on Nov 10, 2008
SQL Injection is an attack technique used to exploit web sites by altering backend SQL Statements through manipulating application input. It is a technique that takes advantage of non-validated user input data to inject SQL Statements dynamically into the existing ones for execution, and hence, poses a severe security threat to the supposedly secure systems. If attacker can get through some of the system stored procedures and some database server functions, then not only they can steal data from the databases, but also modify and delete it.
Some Examples of SQL Injection attack:
Example 1:
If there is a form which gets value from a Form.
If the form looks like:
Enter Product ID: 124 OR 2=2
Then the corresponding SQL Statement for that will be
select * from products where ProductId = 124 OR 2=2
This statement will always return a Value since 2=2 is always true.
Example 2:
If there is a login validation form and a user inputs like this then,
Login: ‘OR”=’
Password: ‘OR”=’
then the corresponding SQL statement will be
Select IsAuthorized FROM Users where Login = ' ' OR"=" AND Password = ' ' OR"="
Read the rest of this entry »
